A Month of SQLPS: Security

One of the frequent questions I get about the SQL Server provider is “Who is it when it connects to the SQL Server?” Security is important to DBAs and, as we’ve seen, there’s a lot of things that can be done through the provide that can be dangerous if we’re not careful. Just like any other tool, we want to make sure that the user in the SQL Server provider can only do what they are allowed to do.

Let’s go back to our extended events session. Since I didn’t share it Friday, here’s the T-SQL for creating the session:

CREATE EVENT SESSION [SQLPS_Tracking] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION(package0.collect_system_time
,sqlserver.client_app_name
,sqlserver.client_hostname
,sqlserver.server_principal_name
,sqlserver.database_name)
WHERE ([sqlserver].[client_app_name] like N'SQLPS%'))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\SQLPS_Tracking.xel');

ALTER EVENT SESSION [SQLPS_Tracking] ON SERVER STATE = START;

This is a simple session that will capture SQL statements that were executed, where they were executed, and by whom. We filter on any application that starts with ‘SQLPS’, since this is how SQL Provider actions will show up in SQL Server. With that running, let’s perform some action and see who we are:

dir SQLSERVER:\SQL\localhost\DEFAULT\databases

This will give us a list of our databases on the default instance. Since we know this boils down to a SQL statement, let’s see what our extended events session captured:

It should not be a surprise that the user we’re working on is our Windows login for our Powershell session. We’re essentially passing our credential through to the SQL Server. This means the reason I’ve been able to do all the things I have is because I have high enough permissions to do all these things.

To validate, I’ve created a user in my SQL lab called SDF\SQLTest. I’ve created this user and given him the db_datareader role in AdventureWorks2012. Let’s see what happens:

#Launch powershell.exe with runas SDF\SQLTest
whoami
cd SQL\localhost\DEFAULT\databases\AdventureWorks2012

#Get only tables in dbo schema
dir Tables | Where-Object {$_.Schema -eq ‘dbo’}

We’re able to see the tables with no problem. The db_datareader role grants us SELECT rights on all objects within the AdventureWorks2012 database. Let’s try and drop something now:

Remove-Item Tables\dbo.ErrorLog

The red text tells the whole story. Looking at this error, we can see the familiar T-SQL message ‘Cannot drop the table….it does not exist or you do not have permissions.’ This clearly shows that permissions are enforced using the provider, so even if you can connect you can still only do what your login will allow.

This default behavior is great, because not only are we restricting the activities of our logins, by default the only logins that can connect are Windows logins. This adds all the typical domain account protections that we get within our active directory. However, what if we want to connect using a SQL credential? There is a way to do this, but it’s a little more obscure. Tomorrow, we’ll review how we can do that along with providing some handy shortcuts to SQL Server components.