Failed to connect to database server. How do I connect to a database that is not on my localhost using powershell and integrated security?

Question

Background to my question

At any moment I am expecting the security people in black suits and black sun glasses to come and take me away because of all my sql server login attempts...

I used and adapted iris classon's example to connect to a database via Powershell. The adapted code uses Integrated Security=True"

$dataSource = my_enterprise_db_server
$database = my_db
$connectionString = "Server=$dataSource;Database=$database;Integrated Security=True;" 
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$table = new-object “System.Data.DataTable”
$query = "..."
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText  = $query
...

Hot diggity dog that worked. Thank's Iris.

I read the snapin verses the Import-Module sqlps way of executing a sql command. I also read all the links that Michael Sorens provided in his answer. I can mount a sqlserver connect with mount mydb SQLSERVER SQLSERVER:\SQL, use ls or dir, walk the path down the objects, etc. I also revised the main part of what Iris provided to

$table = Invoke-Sqlcmd –Server $dataSource –Database $database -Query $query

This version of Invoke-Sqlcmd allows me to connect to an "enterprise" database. The problem with all the references provided are that they expect you to work with a localhost sqlexpress database. The moment I try to use

Set-Location SQLSERVER:\SQL\my_enterprise_db_server\my_db

or similar constructs, I receive a message that ends with

...WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'my_enterprise_db_server' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

I also saw mention of the SQLCMDSERVER and SQLCMDDBNAME environment variables. I set these to

$env:SQLCMDDBNAME = "my_db"
$env:SQLCMDSERVER = "my_enterprise_db_server"
set-location sqlserver:\sql
ls

produces

MachineName                     
-----------                     
localhost   

Question

How do I correctly use set-location or New-PSDrive-Name for a database that does not reside on my local computer?

Answer 1

I found the answer by a serendipitous route. I right clicked on a database object in sql server management studio. There was an option to start powershell. Even though this looks like the order sqlps option, SSMS gave me the right way to set the location.

Option 1. If the server does not have instances, then add DEFAULT after the server_name in the slashy path.

Set-Location SQLSERVER:\SQL\server_name\DEFAULT\Databases\database_name\Tables\dbo.table_name

Option 2. If you have a server with an instance, then set the instance name after the server_name in the slashy path.

Set-Location SQLSERVER:\SQL\server_name\instance_name\Databases\database_name\Tables\dbo.table_name

I am a mere mortal as far as database security goes. Many of the features of SSMS are turned off to me because of my security settings verses how the dba security settings are configured. I receive errors in in SSMS all the time. Well that is no different with Powershell using the Set-Location. I did not realize that the two error messages where related because of the security policy configuration verses pilot error. If I set a location to a table, then I only have two warnings of access denied. If I set the location to the database level, then Powershell blows chunks for a bit but I have my slashy path setting. I do not see the errors if I used the Invoke-SqlCmd. I see now that the way the security errors were presented in Powershell are why I thought there was a problem with how I was attempting to connect to the database. Now I can do this:

mount rb SELSERVER SQLSERVER:\SQL\server_name\DEFAULT\Databases\database_name\Tables
# Look at a list of tables.
ls
# Go to a traditional file system
cd F:\
# Go to the Linux Style mounted file system
cd rb:\
# Go to a table like a directory
cd dbo.my_table_name
# Look at the column names
ls
# Use relative navigation
cd ..\dbo.my_other_table_name
ls
# Compare column names with another table using relative navigation after I have just
# listed the current directory/table that I am in.
ls ..\dbo.my_table_name

That just rocks! Now all I need to do is come up with an array of server names and databases to create mount points for all the databases that I can connect to. An array like that is just begging for an iteration to create all the mount points.