Tag / SQL Security

by Arvind Shyamsundar

Managed Service Accounts (MSA) and SQL 2012: Practical Tips

One of the most common dilemmas for SQL Server administrators is whether they should use AD based domain users as the service accounts, or can they leverage the inbuilt accounts like Network Service etc. If your SQL Server instance is never going to participate in any cross-server contexts (such as availability Groups, Linked Servers, Log Shipping etc.) then you may very well be happy to use one of the inbuilt accounts or even better, in SQL 2012, the special ‘Virtual Account’ feature.

While these options relieve you from the overhead of periodically changing and syncing the service account password, they do impose the machine boundary and cause problems when you want to ‘jump’ across instances. So the classic solution has been to grit one’s teeth and ask for a domain user which will then be configured as the service account. However this brings the hassle of periodic password maintenance, and more importantly that causes downtime.

OR you can configure the SQL 2012 standalone instance to utilize the new Managed Service Accounts feature in Windows 2008 R2 and above. To do this, you follow the steps below.

Setup the MSA in Active Directory

First, create a new MSA in the AD using the PowerShell cmdlet. To do this, there are some simple considerations documented here, but the most important ones are to be an domain administrator, to be on Windows 2008 R2 or above and have the right PowerShell modules installed (Remote Server Administration Tools has a AD PowerShell module which you must install.)

Import-Module ActiveDirectory

New-ADServiceAccount –Name TestSQLMSA -Enabled $true

Next, associate the above MSA with the computer you wish to use it on. Note that a MSA in itself can only be used on one destination computer at a time. In this case, I will specify my lab computer named W2K8R2CN3.

Add-ADComputerServiceAccount -Identity W2K8R2CN3 -ServiceAccount TestSQLMSA

Install the MSA on the target server

Once this is done, you switch to the W2K8R2CN3 computer (which just happens to be a Windows 2008 R2 Core installation) and ‘install’ the MSA on the computer. Now, if you are on Windows 2008 R2 Core installation, to use the ActiveDirectory cmdlets you must effectively install the RSAT-AD-PowerShell feature, but directly using DISM:

DISM /online /enable-feature /featurename=ActiveDirectory-PowerShell

Once that is done, you can easily associate the MSA on the target computer:

Install-ADServiceAccount TestSQLMSA

Important: To do this correctly, you must be a domain administrator. If you run the Install-ADServiceAccount cmdlet and you are not a domain admin, it silently exits, but later when you try to change the service account and start SQL, you will receive an error:

“Error 1069: The service did not start due to a logon failure” and HRESULT 0x8007042d.

If you get those errors, have a domain admin logon to the target server and re-run Install-ADServiceAccount for you.

Change the SQL Service account

Last but not the least: you use SQL Configuration Manager to execute the service account change. An important note is that when specifying the ‘user name’ for the MSA, you must fully qualify it with domain name, and have a trailing $ sign as well. If you do not specify the $ you will receive the error message:

“Invalid parameter [0x80041008]”

As an example, in my test setup, here is how Configuration Manager looks:

image

Supportability questions

MSA and older SQL versions: Though you may be able to configure and / or use MSA with SQL 2008 R2 or older versions, it is officially not tested. If you notice, the documentation for SQL 2008 R2 service accounts is noticeably silent on this topic.

Group Managed Service Accounts (GMSA): As of Feb 2014, Group Managed Service Accounts are NOT officially supported with SQL 2012 Failover Clustered Instances. This is under review by the Product Group for future releases.

References

Please leave your questions, comments below! I’d be very happy to hear from you. Till next time, bye!

by Arvind Shyamsundar

What happens next? SQL with Windows login / multiple groups…

This is a very common case: take a Windows user who belongs to multiple groups. Next, grant more than one of those groups as logins to SQL Server. The question then is: which permissions will that session inherit?

The answer it turns out is that the permissions will be additive and most restrictive. When a Windows login user authenticates to SQL Server, the login token is populated with ALL the groups that this user is associated with. You can view this information through the sys.login_token view. The information in this can be joined with sys.server_principals to get more information. At the database level, you can view the user token using the sys.user_token view.

Additive: Example

Here is a simple example of what I am talking about. Windows user contosomultigroup belongs to two groups, as can be seen here in the output of whoami /groups:

CONTOSOgroup2                   Group            S-1-5-21-1304351064-1365540280-1243399584-1119 Mandatory group, Enabled by default, Enabled group
CONTOSOgroup1                   Group            S-1-5-21-1304351064-1365540280-1243399584-1118 Mandatory group, Enabled by default, Enabled group

We then grant these groups a login and map those logins to a test database:

USE [master]
GO

CREATE LOGIN [CONTOSOgroup1] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

CREATE LOGIN [CONTOSOgroup2] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

USE [testdb]
GO

CREATE USER [CONTOSOgroup1] FOR LOGIN [CONTOSOgroup1]
GO

CREATE USER [CONTOSOgroup2] FOR LOGIN [CONTOSOgroup2]
GO

Here is the information for the login token:

principal_id    sid    name    type    usage
2    0x02    public    SERVER ROLE    GRANT OR DENY
268    0x01050000000000051500000058D1BE4DB87D6451A0C51C4A5E040000    CONTOSOgroup1    WINDOWS GROUP    GRANT OR DENY
269    0x01050000000000051500000058D1BE4DB87D6451A0C51C4A5F040000    CONTOSOgroup2    WINDOWS GROUP    GRANT OR DENY

As you can see, the login token includes both groups. Next, let us view the user token within the TestDB database:

principal_id    sid    name    type    usage
0    0x01050000000000090400000083741B006749C04BA943C02702F2A762    public    ROLE    GRANT OR DENY
5    0x01050000000000051500000058D1BE4DB87D6451A0C51C4A5E040000    CONTOSOgroup1    WINDOWS GROUP    GRANT OR DENY
6    0x01050000000000051500000058D1BE4DB87D6451A0C51C4A5F040000    CONTOSOgroup2    WINDOWS GROUP    GRANT OR DENY

This should confirm what we talked about earlier – that the membership is additive.

Most Restrictive: Example

If we now proceed to deny login permissions to GROUP1:

DENY CONNECT SQL TO [CONTOSOgroup1]
GO

Then our login for CONTOSOmultigroup fails:

Login failed for user ‘CONTOSOmultigroup’. (Microsoft SQL Server, Error: 18456)

Conclusion

The SQL Server engine security model is very powerful and flexible. At the same time it can be confusing in some scenarios. Hopefully, in this post I have cleared up an FAQ item around multiple group memberships for a Windows user; and how the effective permissions are both additive and also most restrictive.

That’s it for now! See you soon!