CODEWITHSUNDEEP
sqlsql-serverpowershell
matandked
matandked

Reputation: 1565

Unable to enumerate databases on SQL Server from PowerShell

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."

Upvotes: 2

Views: 2850

Answers (1)

Adi Inbar
Adi Inbar

Reputation: 12323

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.

Upvotes: 1

Related Questions