Unable to enumerate databases on SQL Server from PowerShell

Question

I wish to check content of one database on server where I'm able to log into by means of Windows Authentication. Sounds really simple and many examples are provided over the Internet.

I tried few examples and each fails on my machine. I suspect, that there might be problem during credentials conversion.

My code (shortened) is as follows:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

$User=[System.Security.Principal.WindowsIdentity]::GetCurrent().Name
$credentials = Get-Credential $saUser | Select-Object *
$Pwd = $credentials.Password | ConvertFrom-SecureString 

$targetConn = New-Object ('Microsoft.SqlServer.Management.Common.ServerConnection') ('myServer', $User, $Pwd)
$targetServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $targetConn

till now there's no error message.

When I type $targetServer, I don't see any objects listed (no Databases as well).

When I tried to check $targetServer.Databases, I received:

The following exception was thrown when trying to enumerate the collection: "Failed to connect to server mmyServer."

Adi Inbar · Accepted Answer · 2014-07-21 16:45:23Z

ConvertFrom-SecureString converts a secure string into an "encrypted standard string" (a hash, which is intended to store the encrypted string in text format). So, you're providing a password hash ($Pwd) as the password argument when creating the $targetConn object, which is invalid.

You can get the plaintext password from the PSCredential object $credentials this way:

$Pwd = $credentials.GetNetworkCredential().Password

However, according to the documentation for the contructors for the ServerConnection class, you can also provide a secure string as the password argument. So it should work if you simply leave out the | ConvertFrom-SecureString, i.e.

$Pwd = $credentials.Password

That's probably a better idea, since it's a little more secure. If you use the first method to get the plaintext password, there's a possibility that the RAM location that stores the $Pwd variable will be paged out while the script is running, resulting in the plaintext password being written to the disk.

Collectives™ on Stack Overflow

Unable to enumerate databases on SQL Server from PowerShell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related