TL;DR; Explains how to enable Windows Authentication for SQL Server connections via DNS by configuring the SQL Server Browser service and setting up Service Principal Names (SPNs).
If you want to connect to a SQL server using DNS you will need to have both the “SQL Server Browser” service and an SPN setup in your domain to allow authentication.
This allows the DNS name that you have setup to authenticate against the SQL Server using integrated authentication.
setspn MSSQLSvc/spdata.ep-dev.[domain].biz:1422 [servername]
setspn MSSQLSvc/spdata.ep-dev.[domain].biz:1433 [servername]
You will know that you need to do this if all of the below is true:
You can get he setspn tool from the Windows Server 2003 Admin Pack.
Use it to see what SPN’s already exist and to see how you can utilise them. For example calling setspn on my [servername] produced:
C:>setspn [servername]
Registered ServicePrincipalNames for CN=[servername],OU=Member Servers,DC=[domain],DC=biz:
MSSQLSvc/[servername].[domain].biz:1422
MSSQLSvc/[servername].[domain].biz:1433
SMTPSVC/[servername]
SMTPSVC/[servername].[domain].biz
HOST/[servername].[domain]onet.biz
HOST/[servername]
this information helped me identify that the first two “MSSQLSvc” spn’s would need to be added to this server. You can add SPN’s to accounts as well, but if you only need to access a single server then adding it to an account is a little overkill and probably a security risk.
I’m off to make a request for infrastructure to run this…
Technorati Tags: SP 2007
Each classification [Concepts, Categories, & Tags] was assigned using AI-powered semantic analysis and scored across relevance, depth, and alignment. Final decisions? Still human. Always traceable. Hover to see how it applies.
If you've made it this far, it's worth connecting with our principal consultant and coach, Martin Hinshelwood, for a 30-minute 'ask me anything' call.
We partner with businesses across diverse industries, including finance, insurance, healthcare, pharmaceuticals, technology, engineering, transportation, hospitality, entertainment, legal, government, and military sectors.
Workday
Slaughter and May
Schlumberger
Jack Links
Trayport
Bistech
Genus Breeding Ltd
Milliman
Ericson
Flowmaster (a Mentor Graphics Company)
Freadom
Kongsberg Maritime
NIT A/S
Philips
Akaditi
Higher Education Statistics Agency
Qualco
Illumina
Department of Work and Pensions (UK)
Washington Department of Transport
Royal Air Force
Nottingham County Council
Washington Department of Enterprise Services
New Hampshire Supreme Court
Epic Games
Hubtel Ghana
Freadom
Capita Secure Information Solutions Ltd
ALS Life Sciences
Alignment Healthcare