x

Starting Powershell from SSMS using SQL authentication

I have a SQL server in a hosted environment where all connectivity is through SQL authentication.

So in SSMS 2008, I right click in Object Explorer and get the 'Start PowerShell' option, but when I click I get a PS window with

New-PSDrive : SQL Server PowerShell provider error: Could not connect to 'SERVERXYZ\DEFAULT'. [Object reference not set to an instance of an object.]

At line:1 char:701 + &{[System.Console]::Title = 'SQL Server Powershell';$cdpath = (Convert-UrnToP ath 'Server[@Name=''SERVERXYZ'']') + '\';$root=$cdpath.Substring(0, $cdpath.Index Of('\', $cdpath.IndexOf('\', $cdpath.IndexOf('\', $cdpath.IndexOf('\')+1)+1)+1) );$pwd = ConvertTo-SecureString '01000000d08c9ddf0115d1118c7a00c04fc297eb010000 004cc6f6478b1e7741bb5ce156959130190000000002000000000003660000a800000010000000a ca72b315b660427431655088d27f8750000000004800000a0000000100000007c488d0e15beff57 a1874c8c231ec8b9180000003e773b017a51cf68c51e9d1e12df81ed1233be8fa7386b651400000 05bad64836f8a42e6fb9b5a5185dd89b35f70d663';$cred = New-Object System.Management .Automation.PSCredential -ArgumentList 'sa',$pwd;$newroot=(New-PSDrive <<<< 33 6a5d2eb2714e78846160a3ae7298c1 -PSProvider SqlServer -Root $root -Credential $c red -Scope 1).Root;cd 336a5d2eb2714e78846160a3ae7298c1:;$cdpath.Replace($root, $newroot)|cd}

New-PSDrive : SQL Server PowerShell provider error: Path SQLSERVER:\SQL\SERVERXYZ \DEFAULT does not exist. Please specify a valid path.

At line:1 char:701 + &{[System.Console]::Title = 'SQL Server Powershell';$cdpath = (Convert-UrnToP ath 'Server[@Name=''SERVERXYZ'']') + '\';$root=$cdpath.Substring(0, $cdpath.Index Of('\', $cdpath.IndexOf('\', $cdpath.IndexOf('\', $cdpath.IndexOf('\')+1)+1)+1) );$pwd = ConvertTo-SecureString '01000000d08c9ddf0115d1118c7a00c04fc297eb010000 004cc6f6478b1e7741bb5ce156959130190000000002000000000003660000a800000010000000a ca72b315b660427431655088d27f8750000000004800000a0000000100000007c488d0e15beff57 a1874c8c231ec8b9180000003e773b017a51cf68c51e9d1e12df81ed1233be8fa7386b651400000 05bad64836f8a42e6fb9b5a5185dd89b35f70d663';$cred = New-Object System.Management .Automation.PSCredential -ArgumentList 'sa',$pwd;$newroot=(New-PSDrive <<<< 33 6a5d2eb2714e78846160a3ae7298c1 -PSProvider SqlServer -Root $root -Credential $c red -Scope 1).Root;cd 336a5d2eb2714e78846160a3ae7298c1:;$cdpath.Replace($root, $newroot)|cd}

Set-Location : Cannot find drive. A drive with name '336a5d2eb2714e78846160a3ae 7298c1' does not exist.

At line:1 char:804 + &{[System.Console]::Title = 'SQL Server Powershell';$cdpath = (Convert-UrnToP ath 'Server[@Name=''SERVERXYZ'']') + '\';$root=$cdpath.Substring(0, $cdpath.Index Of('\', $cdpath.IndexOf('\', $cdpath.IndexOf('\', $cdpath.IndexOf('\')+1)+1)+1) );$pwd = ConvertTo-SecureString '01000000d08c9ddf0115d1118c7a00c04fc297eb010000 004cc6f6478b1e7741bb5ce156959130190000000002000000000003660000a800000010000000a ca72b315b660427431655088d27f8750000000004800000a0000000100000007c488d0e15beff57 a1874c8c231ec8b9180000003e773b017a51cf68c51e9d1e12df81ed1233be8fa7386b651400000 05bad64836f8a42e6fb9b5a5185dd89b35f70d663';$cred = New-Object System.Management .Automation.PSCredential -ArgumentList 'sa',$pwd;$newroot=(New-PSDrive 336a5d2e b2714e78846160a3ae7298c1 -PSProvider SqlServer -Root $root -Credential $cred -S cope 1).Root;cd <<<< 336a5d2eb2714e78846160a3ae7298c1:;$cdpath.Replace($root, $newroot)|cd}

PS SQLSERVER:\>

and at the same time get errors on the target server

SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed.

Simple enough - the connection has been attempted with Windows authentication, but my local domain credentials are not recognised by the remote hosted server.

Question is, how can I connect to PowerShell, from the SSMS menu, using SQL authentication? Is that even possible?
more ▼

asked Oct 06 '10 at 02:12 AM in Default

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

Sounds like one for James Boother... I've put out a tweet for him.
Oct 06 '10 at 05:13 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Kev - quick search brought up this:

http://msdn.microsoft.com/en-us/library/cc281947.aspx

Search for the text "SQL Server Authentication" and this first hit shows that Powershell only uses Windows authentication.

HTH

EDIT: Found this tip that suggests it is possible: [http://www.mssqltips.com/tip.asp?tip=1947][2]

[2]: http://www.mssqltips.com/tip.asp?tip=1947
more ▼

answered Oct 06 '10 at 03:17 AM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

@WilliamD - yep had seen that link - thought that was more about setting a SQL auth connection, once you are into PS, just wasn't sure if there was a way in SSMS somewhere that would allow SQL/Windows to be specified.
Oct 06 '10 at 03:38 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

You can do it, but you have to set up a virtual drive first. [Check out this][1] from the BOL, scroll down to "Managing SQL Server Authentications". You can use invoke-sqlcmd and pass it a SQL login & password. You can also use SMO through PowerShell and connect using SQL authentication.

[1]: http://msdn.microsoft.com/en-us/library/cc281947.aspx
more ▼

answered Oct 06 '10 at 06:11 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.6k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x123
x24
x18

asked: Oct 06 '10 at 02:12 AM

Seen: 2439 times

Last Updated: Oct 06 '10 at 05:13 AM