Art of the DBA Rotating Header Image

Security

Managing SQL Server Services with #Powershell

14711793077_7088d420cf_zManaging service accounts is one of those tedious jobs that tend to annoy me. Changing the passwords of these accounts regularly is a good security practice, but takes a lot of time and can be difficult to manage. In other words, a perfect task to automate with Powershell.

There are two ways to handle this task, both through the Windows Management Instrumentation(WMI). The first way uses the base WMI interface, which can be used to manage all Windows services. Using it is a little convoluted, but gets the job done:

$svc = Get-WmiObject -Class Win32_Service -ComputerName PICARD -Filter 'Name = "MSSQLSERVER"'
$svc.Change($Null,$Null,$Null,$Null,$Null,$Null,'sdf\sqlsvc2','P@$$w0rd',$Null,$Null,$Null)

This call is easy to decipher. Using the .Change() method of the service class, we can update the service account name and/or password (as well as other properties of the service). You probably noticed the number of arguments the .Change() method takes, which makes it cumbersome to use. The other gotcha is that the service still needs to be restarted in order for these changes to take affect. Depending on your need, these gotchas can be good or bad, but can be handled depending on how you code around it.

If you’ve managed services through the GUI, using this method probably makes you think of how you manage accounts through the services MMC console. However, most SQL Server folks will use the SQL Server Configuration console instead. These two methods are subtly different, where using the SQL Server Configuration console will handle some additional tasks (such as restarting the service) as part of its interface. If we want to manage our SQL Services in the same fashion, we can leverage a part of the SMO, the Wmi.ManagedComputer Wmi.Service classes.

To handle our services, we need an extra step or two, but it’s a little cleaner to write:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement')| Out-Null
$smowmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer PICARD
$wmisvc = $smowmi.Services | Where-Object {$_.Name -eq $ServiceName}
$wmisvc.SetServiceAccount('sdf\sqlsvc2','P@$$w0rd')

We first need to load the SqlWmiManagement assembly, just like loading the SMO libraries if we were using that functionality(note: this library is loaded if you load the SQLPS module). Then we need to instantiate the Managed computer object and retrieve the specific service we want to alter. The final step is to just change the service account.

This works about the same as the base WMI approach, though we’re altering the service by using the same functionality as the SQL Server Configuration Manager. This means that once we change the service account, it will force a service restart. This is good and bad. The good is that it will apply the change immediately and you will know right away if the account change is valid. The bad is that you can not delay the service restart, so if you use this method you want to be sure it is a good time to restart your SQL Service.

I have built a function around using the second method that makes handling this process a little easier. Also, because I’m not a fan of passing passwords in plain text, I built the function to take a PSCredential object to keep my account information secure. In order to spare you the wall of text, you can view the full function on my GitHub repository.

The function can be loaded through a variety of methods, but once it is loaded calling it is simply a matter of creating the credential for the service account and calling the function:

$cred = Get-Credential 'Enter Service Account'
Set-SqlServiceAccount -Instance PICARD -ServiceAccount $cred -Service SqlServer

Creating functions allows us to abstract some of the messy bits and make our lives a little easier. In this case, my function handles the following:

  • Decoding the credential in a way to keep the account information secure.
  • Managing the service names based on the instance name (passed in the standard HOST\INSTANCE name format).
  • Restarting the SQL Server Agent service if it is not running after up restart the SQL Server service.
  • Accept a list of instances and process all of them.

This simplifies the changing of account information and gives us many opportunities for automating large scale password changes. For example, if you use a single service account for all your instances, changing it is a snap:

$servers = @('PICARD','RIKER','KIRK','SPOCK')

$cred = Get-Credential 'Enter Service Account'
Set-SqlServiceAccount -Instance $servers -ServiceAccount $cred -Service SqlServer

This simple pattern and function will not only make managing our security policy easier, but also more consistent. Using a simple list of servers from a text file, a database table, or even our Central Management Server and combining it with this function means we ensure that we are applying these changes to every server in the list. This is how we can build for automation, focusing on making simple tasks like this repeatable and consistent.

Quick hat tips out to the following folks:

#tsql2sday 63 – Security, Default Databases, and #Powershell

This month’s T-SQL Tuesday is about how we manage database security, hosted by the ever effervescent Kenneth Fischer(@sqlstudent144). While I didn’t have a lot of time to work on this post, I wanted to share with you all a quick little nugget I used for one of my most recent presentations.

So, default databases. This is a security bit that usually gets skipped over, unfortunately, but I think it’s a pretty important practice that is easy to implement and maintain. Let’s be honest, most of us leave the default database as ‘master’ when we create them. This then will usually lead us to accidentally doing work in the master, which potentially could be disastrous.

2042603602_fc289cf395_zSecurity is more than just locking people out or letting people in. While most of the situations with master can be be limited by reduced permissions, it doesn’t do anything to help or manage accounts that need to be able to work in that database.  Security is not just about active policies, but also passive rules to reduce and prevent mistakes. A lot of damage can be prevented by directing people out of dangerous places by default.

Fixing this is easy:

ALTER LOGIN [foo\bar] WITH DEFAULT_DATABASE = [tempdb];

Also, we’d ideally create the logins with that default database set to begin with:

CREATE LOGIN [foo\bar] FROM WINDOWS WITH DEFAULT_DATABASE=[tempdb];

However, this can be difficult to manage and keep up to date, especially since SQL Server doesn’t help by always defaulting to master. One thing that can be done is to have a way to set all your logins’ default databases to an appropriate database (I really like tempdb for this). So how about some Powershell?

Using the SMO, we can use the DefaultDatabase property of the Login class for a quick and easy way to alter our default database:

$login.DefaultDatabase = ‘tempdb’

Then, using the Logins collection in our Server object, we have an easy loop to go through an alter these default databases. Wrap that up in a fucntion and Bob’s your uncle. Well, he’s my uncle, not yours. Anyway, we’ll get a little something like this:

#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$ErrorActionPreference = 'Inquire'

function Set-SQLDefaultDatabases{
    param([string[]]$Instances = 'localhost'
         ,[string]$defaultdb = 'tempdb')

foreach($InstanceName in $Instances){
    $smosrv = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server $InstanceName
    if($smosrv.Databases.name -contains $defaultdb){
        foreach($login in $smosrv.Logins){
            Write-Verbose "Altering $login on $InstanceName to $defaultdb"
            $login.DefaultDatabase = $defaultdb
            }
        }
    else{
        Write-Warning "Database $defaultdb is not valid on $InstanceName."
        }
    }
}

Set-SQLDefaultDatabases

So this simple function will set all the logins (SQL, Windows, and AD Groups) to the default database of choice. It’s fairly brute force, but gives you a good starting point. At this point, you should be able to see the value of writing it in this function format and use techniques I’ve blogged about earlier to set this enterprise wide.

Thanks out to Ken for hosting this month’s T-SQL Tuesday! Looking forward to reading the other blogs!

Getting Tooled

This week Tom LaRock(@sqlrockstar) tweeted a question, followed by a full on blog post and survey, asking folks if they installed client tools on their servers.  My answer was pretty blunt:

 

This got wrapped up in a larger discussion about whether or not installing client tools is appropriate, with some strong (and not necessarily wrong) opinions on either side.  I confess I didn’t get involved, mostly because I find it hard to have a serious discussion in 140 character snippets.  So now I’ll blog about it! 

I’m the kind of DBA that is a “jerk”.  I say no a lot and prefer, in production, not to take any more action than absolutely necessary until it’s proven that the action will do no harm.  I don’t get off on it nor do I enjoy giving people the hand.  I’m just…..careful.  We’ve all been burned and it’s my responsibility to make sure that we’re protecting the company’s assets, both data and the systems that data lives on. 

The problem with client tools is they can create avenues for danger.  For example, if I install SQL Server Management Studio and the Sysinternals tools, I’ve created a way for a local administrator on that server to log in to my SQL Server as an administrator, even if it wasn’t my intention to grant him that access.  This can be extremely useful (such as a situation where you do lose your SQL admin logins), but there’s inherent risk there.  Other tools can create similar risk, so my view is to try and reduce this risk by minimizing client tool installs. 

Another problem with client tool installs is that client tools take resources away from SQL Server (or other processes that the server is hosting up).  I know, I know…we live in an age where RAM and CPU are plentiful, but I still get protective of my stuff.  These are MY toys and, since I’m a jerk, I hate sharing.  By restricting client tool installs, I proactively prevent this sort of sharing and keep folks out of my sandbox. 

Thirdly, by putting client tools on to server, I provide a crutch for those who feel they have to do all their work directly on the server.  This is a bad practice, even if you have resources.  You’re not just taking stuff away from SQL, things you could also seriously damage something without even intending to.  Accidentally shut down the box?  Easy.  Delete or corrupt critical files?  Piece of cake.  I often think of working on the server as playin around in the middle of a minefield and why put a tool I need in the middle of that minefield?  If the tool isn’t installed, there’s no reason to log in to a server, so it doesn’t even become an issue.

This is one of many reasons I’m so high on Server 2012 Core.  By the very lack of its GUI, a lot of people will shy away from tools.  And the tools are there, but let’s be honest with each other here:  Most of us Windows folks love our dialog boxes and ‘Ok’ buttons, our drop ‘n drag paired with right clicking.  Command line interfaces are an anathema and we will avoid them as a preference.

 I get it, though.  There are plenty of valid reasons why you would install those tools.  It can speed up troubleshooting and there are certain things that can only be done from the machine itself.  Plus, if your box is secure, you can reduce the risk of having those tools out there.  I would argue that, even with the box secure, minimizing your client tool installs will reduce your risk even further.

I challenge folks to really, REALLY ask themselves: Do you really need use those client tools on the server?  In most cases, you probably don’t.  And if you don’t need to use them on the server, then why are you installing them in the first place?

First post and first presentation!

Last Thursday, I got a chance to present to the Denver SQL Users Group on Managing and Auditing SQL Security. I crammed a lot in for 30 minutes and got some good immediate feed back. I think the one thing that made me nervous was no one really asked any questions, but since it was my first technical presentation in a long time, I’m not sweating it.

Anyway, for those of you who attended, thanks for listening. If you have a chance, please leave me some feedback at Speaker Rate. I’d really appreciate it. For those who didn’t make it, the presentation was an overview of creating accounts in SQL Server, granting access via GRANT, server roles, and database roles, as well as queries that you can use to audit that access.

Here’s the meat for all those interested:

Powerpoint Presentation – SQL Server Security
(Please note the presentation is released under the Creative Commons Attribution-NonCommercial license)

Scripts:
server_logins.sql
server_roles.sql
server_role_audit.sql – Uses xp_logininfo
database_roles.sql
db_roles_audit.sql – Uses xp_logininfo
object_grants.sql

Enjoy!

Last Thursday, I got a chance to present to the Denver SQL User’s Group on Managing and Auditing SQL Security. I crammed a lot in in 30 minutes and got some good immediate feed back. I think the one thing that made me nervous was no one really asked any questions, but since it was my first technical presentation in a long time, I’m not sweating it.

 

Anyway, for those of you who attended, thanks for listening. If you have a chance, please leave me some feedback at Speaker Rate for me. I’d really appreciate it. For those who didn’t make it, the presentation was an over view of creating accounts in SQL Server, granting access via GRANT, server roles, and database roles, as well as queries that you can use to audit that access.

 

Here’s the meat for all those interested:

Powerpoint Presentation – SQL Server Security

 

Scripts:

server_logins.sql

server_roles.sql

server_role_audit.sql – Uses xp_logininfo

database_roles.sql

db_roles_audit.sql – Uses xp_logininfo

object_grants

Last Thursday, I got a chance to present to the Denver SQL User’s Group on Managing and Auditing SQL Security. I crammed a lot in in 30 minutes and got some good immediate feed back. I think the one thing that made me nervous was no one really asked any questions, but since it was my first technical presentation in a long time, I’m not sweating it.

Anyway, for those of you who attended, thanks for listening. If you have a chance, please leave me some feedback at Speaker Rate for me. I’d really appreciate it. For those who didn’t make it, the presentation was an over view of creating accounts in SQL Server, granting access via GRANT, server roles, and database roles, as well as queries that you can use to audit that access.

Here’s the meat for all those interested:

Powerpoint Presentation – SQL Server Security

Scripts:

server_logins.sql

server_roles.sql

server_role_audit.sql – Uses xp_logininfo

database_roles.sql

db_roles_audit.sql – Uses xp_logininfo

object_grants