November 7, 2016 at 5:30 pm
We use a domain account for SQL service account.
But now we would like to try to use Managed service account for SQL sever 2016.
For SQl server service, SQL server agent service, SSIS service and SSRS service.
Anyone has experience of using managed service account for SQL server 2016
Any pros and cons and things need to pay attention to?
November 8, 2016 at 9:23 am
I've used them since SQL 2012. The only issues I had is with setting up kerberos delegation, but this site explains how to get it working:
http://ityogi.se/wordpress/msas-and-kcd/
Oh, and the only other issue I had was when logging a support call to Microsoft, the SQL "expert" didn't know what they were so had to explain them to him!
November 8, 2016 at 11:16 am
Thank you!
So the article says Kerberos Constrained Delegation, what does constrained mean, is it special?
Can the managed service account be shared on another computer? for example can it be used as SQL service account on dev server, and also on test server? I know it is not a good practice, but just ask if it can be or not.
Also if the server has SSIS package that needs to access other server no matter SQL server or flat files on other servers on our domain, I heard our network person said it may need to add it to a windows group, is that true?
Thanks
November 9, 2016 at 2:24 am
No, the account cannot be shared for another sql service on another server. The account is tied to the server. Even if you were using a normal service account, it would be best practice to separate different service accounts across services and servers.
Constrained delegation just means the Kerberos authentication works on a set of services of a server rather than the everything. It's a much safer form of delegation: https://technet.microsoft.com/en-us/library/jj553400(v=ws.11).aspx
Normally you would set up SPN's on the SQL Server to allow Kerberos delegation to work successfully, but you have to do things differently with the managed service account.
November 9, 2016 at 10:21 am
Thanks,
One other question related to this, I have a SSIS package that export data from this local server that has MSA account to another SQL server.
I need to add MSA account as a login to the destination server to have access to the database, but it gives an error. An object with this name cannot be found.
What could be wrong?
November 10, 2016 at 2:03 am
If you're using the search tool on the New Login screen, it won't find the managed service account and you will get this error - I take it that is where you are seeing it.
To get round this, simple type the MSA name in the Login Name of the new Login screen and give it the permissions. You don't need to search the domain for it. It will get added. Alternatively, just create the login with SQL:
USE [master]
GO
CREATE LOGIN [SQLDomain\Server2_SQL$] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
And then give it the permissions in the database required.
November 10, 2016 at 11:16 am
Thank you, that worked, the trick like you said just enter the login without search in AD.
Thanks,
Also would like to ask how to identify the SQL server is Kerberos authentication, I used below query:
SELECT
s.session_id,
c.connect_time,
s.login_time,
s.login_name,
c.protocol_type,
c.auth_scheme,
s.HOST_NAME,
s.program_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
It returned NTLM, but network admin said he did register SPN. so not sure which part is not done correctly?
Another question, our network admin said if the SSIS package need to access data file like flat file on networkshare, we may also need to add the windows server name of that share to a windows group, so that it can retrieve the password of MSA account, is that necessary?
Thanks,
November 11, 2016 at 2:52 am
Yes, your query should show Kerberos connections if they are using them. To check if the spn has been set up, you could do two things:
Firstly, from a command prompt you can execute the following to list any spn's for the service name:
setspn -l <serviceName>
I.e.
setspn -l DomainName\Server2_SQL$[\code]
This should show the two spns set for the service account. Something a bit like:
MSSQLSvc/Server2.DomaineName.Com:1433
MSSQLSvc/Server2.DomainName.Com:SQLInstanceName
The other way to check is to look at the sql server log in management studio and look at the very beginning of the log where the SQL Server instance was started. You should see a record showing the SPN was successfully registered or not. Something along the lines of:
SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/Server2.DomainName.com:SQLInstanceName ] for the SQL Server service.
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/Server2.DomainName.com:1433 ] for the SQL Server service.
If it fails to register the spn, then you should see a failure message.
For managed service accounts, you should just need to follow the steps in the first link I posted to you.
Regarding you second question about ssis accessing files on a share, all you should need to do is allow make sure the sql agent account has permissions to read (and write if required) to the network share. You shouldn't need to add the computer account to any groups. Another option is to use a Proxy account to run the ssis agent job. The following page explains this in more detail. Look at the Using Proxies section: https://www.simple-talk.com/sql/database-administration/setting-up-your-sql-server-agent-correctly/
November 11, 2016 at 10:39 am
Thank you very much, very helpful, I will give a try
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply