1

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?

Community
  • 1
  • 1
Greg
  • 233
  • 2
  • 12
  • Do you have to? You may not have permissions to locally logon to that server. Are you admin on it? Can you just not point to it: $DataSource="ServerName\InstanceName" – Adil Hindistan Dec 18 '14 at 05:19
  • @AdilHindistan Greg not in ( 'admin', 'rdp', 'dba', 'etc' ). There's no value to having a local database. Do I have to? Nope!. Ahhh but having worked in a Solaris/Linux environment for 20 years and now working in a MS shop, I see the wonderful value of the mount alias that has been applied to New-PSDrive-Name. There are many techniques of creating a directory i.e. mount point and then grafting in other data and what not. With mount you can shorten the path to just the tables. Hence, you can cd, ls, etc just like a directory or file. Checkout Michael Sorens' article in the question above. – Greg Dec 18 '14 at 15:51
  • Are you trying to manage the server or trying to write code for the DB on it? If the former, you would need to have admin perms on the servers for PowerShell remoting/WMI. Devs would not have admin permissions on servers where prod DBs reside, so you can pretty much forget about WMI (though technically it uses DCOM). What M.Soren does makes sense from a "DBA" perspective, or if you are managing a DB locally. If you are a dev, all you care would be to provide conn. string and do whatever you need to do after that without requiring any access to actual server where DB reside. – Adil Hindistan Dec 18 '14 at 16:37
  • @AdilHindistan I find that DBA's sometimes keep too many of the good things too themselves. For how little I can use SSMS because of security policies, I would be better off using Oracle's Sql Developer. That would make me far more effective at developing queries or the using a database. – Greg Dec 19 '14 at 15:40

1 Answers1

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.

Greg
  • 233
  • 2
  • 12