December 16, 2013 at 3:47 am
Hi,
First post here, so thanks in advance.
Trying to set up SPNs for managed service account that are used to access various different DBs on SQL 2012 server. As the domain is not in full 2008R2 Windows server, the SPNs had to be registered manually.
The are duplicate entries present for the SPNs. I'm sure it is just my error when defining, so gratefull for clarification of how this should be done.
I've just shown for the default instance (on port 1433)
setspn -x returns this:
MSSQLSvc/MyServer.MyDomain.com:1433 is registered on these accounts:
CN=MSA_SQL_1,CN=Managed Service Accounts,DC=MyDomain,DC=com
CN=MSA_SQL_2,CN=Managed Service Accounts,DC=MyDomain,DC=com
CN=MSA_SQL_3,CN=Managed Service Accounts,DC=MyDomain,DC=com
CN=MSA_SQL_4,CN=Managed Service Accounts,DC=MyDomain,DC=com
CN=MSA_SQL_5,OU=MSA,OU=Users,DC=MyDomain,DC=com
MSSQLSvc/MyServer.MyDomain.com: is registered on these accounts:
CN=MSA_SQL_1,CN=Managed Service Accounts,DC=MyDomain,DC=com
CN=MSA_SQL_2,CN=Managed Service Accounts,DC=MyDomain,DC=com
These are all accounts that are used on the default instance. For each account I registered using multiple setspn -A commands using FQDN and ports. Probably here where mistake may be e.g.
SETSPN -A MSSQLSvc/MyServer:1433 MyDomain\MSA_SQL_5$
SETSPN -A MSSQLSvc/MyServer.MyDomain.com:1433 MyDomain\MSA_SQL_5$
SETSPN -A MSSQLSvc/MyServer.MyDomain.com MyDomain\MSA_SQL_5$
December 16, 2013 at 7:01 am
colin.hunter (12/16/2013)
Hi,First post here, so thanks in advance.
Trying to set up SPNs for managed service account that are used to access various different DBs on SQL 2012 server. As the domain is not in full 2008R2 Windows server, the SPNs had to be registered manually.
The are duplicate entries present for the SPNs. I'm sure it is just my error when defining, so gratefull for clarification of how this should be done.
I've just shown for the default instance (on port 1433)
setspn -x returns this:
MSSQLSvc/MyServer.MyDomain.com:1433 is registered on these accounts:
CN=MSA_SQL_1,CN=Managed Service Accounts,DC=MyDomain,DC=com
CN=MSA_SQL_2,CN=Managed Service Accounts,DC=MyDomain,DC=com
CN=MSA_SQL_3,CN=Managed Service Accounts,DC=MyDomain,DC=com
CN=MSA_SQL_4,CN=Managed Service Accounts,DC=MyDomain,DC=com
CN=MSA_SQL_5,OU=MSA,OU=Users,DC=MyDomain,DC=com
MSSQLSvc/MyServer.MyDomain.com: is registered on these accounts:
CN=MSA_SQL_1,CN=Managed Service Accounts,DC=MyDomain,DC=com
CN=MSA_SQL_2,CN=Managed Service Accounts,DC=MyDomain,DC=com
These are all accounts that are used on the default instance. For each account I registered using multiple setspn -A commands using FQDN and ports. Probably here where mistake may be e.g.
SETSPN -A MSSQLSvc/MyServer MyDomain\MSA_SQL_5$SETSPN -A MSSQLSvc/MyServer:1433 MyDomain\MSA_SQL_5$
SETSPN -A MSSQLSvc/MyServer.MyDomain.com:1433 MyDomain\MSA_SQL_5$
SETSPN -A MSSQLSvc/MyServer.MyDomain.com MyDomain\MSA_SQL_5$
I would remove the inappropriate duplicate SPN's using setspn -D. The only ones you should need are for the FQDN of the server for the service account, i.e.
SETSPN -S MSSQLSvc/MyServer.MyDomain.com:1433 MyDomain\MSA_SQL_5$
SETSPN -S MSSQLSvc/MyServer.MyDomain.com MyDomain\MSA_SQL_5$
N.B. Use -S instead of -A. -S checks for duplicates when creating the SPN.
December 16, 2013 at 7:23 am
Thanks,
If I clear things up and then add, as suggested the following, where MSA_SQL_5 happens to the the MSA that is running SQL server service:
SETSPN -A MSSQLSvc/MyServer.MyDomain.com:1433 MyDomain\MSA_SQL_5$
SETSPN -A MSSQLSvc/MyServer.MyDomain.com MyDomain\MSA_SQL_5$
For the other MSAs (which are just mapped to database users, for various databases on the server), do I not need to repeat this? Then I would then end up with duplicates (or a warning of duplicates):
E.g. adding this would raise a duplicate warning
SETSPN -S MSSQLSvc/MyServer.MyDomain.com:1433 MyDomain\MSA_SQL_1$
SETSPN -S MSSQLSvc/MyServer.MyDomain.com MyDomain\MSA_SQL_1$
(The domain isn't full 2008 level, so have to manually register).
My understanding was that each of the other MSA accounts would need a SPN registered manually, but is it only the SQL Server service that needs SPN set up?
December 16, 2013 at 8:42 am
sorry - what I wrote before was rubbish. I assumed MSA's worked the same way as regular accounts when it came to SPN's.
See if this blog post helps you:
December 17, 2013 at 5:52 am
Still needing some help with this. Any other thoughts please?
colin.hunter (12/16/2013)
Hi,First post here, so thanks in advance.
Trying to set up SPNs for managed service account that are used to access various different DBs on SQL 2012 server. As the domain is not in full 2008R2 Windows server, the SPNs had to be registered manually.
The are duplicate entries present for the SPNs. I'm sure it is just my error when defining, so gratefull for clarification of how this should be done.
I've just shown for the default instance (on port 1433)
setspn -x returns this:
MSSQLSvc/MyServer.MyDomain.com:1433 is registered on these accounts:
CN=MSA_SQL_1,CN=Managed Service Accounts,DC=MyDomain,DC=com
CN=MSA_SQL_2,CN=Managed Service Accounts,DC=MyDomain,DC=com
CN=MSA_SQL_3,CN=Managed Service Accounts,DC=MyDomain,DC=com
CN=MSA_SQL_4,CN=Managed Service Accounts,DC=MyDomain,DC=com
CN=MSA_SQL_5,OU=MSA,OU=Users,DC=MyDomain,DC=com
MSSQLSvc/MyServer.MyDomain.com: is registered on these accounts:
CN=MSA_SQL_1,CN=Managed Service Accounts,DC=MyDomain,DC=com
CN=MSA_SQL_2,CN=Managed Service Accounts,DC=MyDomain,DC=com
These are all accounts that are used on the default instance. For each account I registered using multiple setspn -A commands using FQDN and ports. Probably here where mistake may be e.g.
SETSPN -A MSSQLSvc/MyServer MyDomain\MSA_SQL_5$SETSPN -A MSSQLSvc/MyServer:1433 MyDomain\MSA_SQL_5$
SETSPN -A MSSQLSvc/MyServer.MyDomain.com:1433 MyDomain\MSA_SQL_5$
SETSPN -A MSSQLSvc/MyServer.MyDomain.com MyDomain\MSA_SQL_5$
December 18, 2013 at 8:39 am
The server itself needs to be registered and allowed to delegate.
The account running the SQL svc needs to be allowed to delegate this service on this machine.
Different accounts, accessing indivifual db's - these are permissions, not SPN's.
See if this link[/url] helps explain things.
Remember that Kerberos is setup so that an account under which a service is run can impersonate another user.
You may want to consider using Roles and Windows groups, rather than accounts granted permissions to each db.
Then you are able to secure based on the logged in user, rather than who may have access to the resource requesting the data.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply