a·gen·tic a·gil·i·ty

Connecting to SQL Server using DNS

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).

Published on
2 minute read
Image
https://nkdagility.com/resources/tV0-x7E8IQc
Subscribe

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:

  1. Pining your domain brings back the correct IP of the server
  2. You can connect to the SQL Server using the server name with Windows Authentication.
  3. You can connect to the SQL server using the IP address with Windows Authentication.
  4. You can NOT connect to the SQL server using the spdata.ep-dev.[domain].biz with Windows Authentication.
  5. You can connect to the SQL server using the spdata.ep-dev.[domain].biz with SQL Server Authentication.

Connecting to SQL Server using DNS Connecting to SQL Server using DNS   Connecting to SQL Server using DNS   Connecting to SQL Server using DNS

Connecting to SQL Server using DNS

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… Connecting to SQL Server using DNS

Technorati Tags: SP 2007

Smart Classifications

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.

Subscribe

Connect with Martin Hinshelwood

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.

Our Happy Clients​

We partner with businesses across diverse industries, including finance, insurance, healthcare, pharmaceuticals, technology, engineering, transportation, hospitality, entertainment, legal, government, and military sectors.​

Workday Logo

Workday

Slaughter and May Logo

Slaughter and May

Schlumberger Logo

Schlumberger

Jack Links Logo

Jack Links

Trayport Logo

Trayport

Bistech Logo

Bistech

Genus Breeding Ltd Logo

Genus Breeding Ltd

Milliman Logo

Milliman

Ericson Logo

Ericson

Flowmaster (a Mentor Graphics Company) Logo

Flowmaster (a Mentor Graphics Company)

Freadom Logo

Freadom

Kongsberg Maritime Logo

Kongsberg Maritime

NIT A/S

Philips Logo

Philips

Akaditi Logo

Akaditi

Higher Education Statistics Agency Logo

Higher Education Statistics Agency

Qualco Logo

Qualco

Illumina Logo

Illumina

Department of Work and Pensions (UK) Logo

Department of Work and Pensions (UK)

Washington Department of Transport Logo

Washington Department of Transport

Royal Air Force Logo

Royal Air Force

Nottingham County Council Logo

Nottingham County Council

Washington Department of Enterprise Services Logo

Washington Department of Enterprise Services

New Hampshire Supreme Court Logo

New Hampshire Supreme Court

Epic Games Logo

Epic Games

Hubtel Ghana Logo

Hubtel Ghana

Freadom Logo

Freadom

Capita Secure Information Solutions Ltd Logo

Capita Secure Information Solutions Ltd

ALS Life Sciences Logo

ALS Life Sciences

Alignment Healthcare Logo

Alignment Healthcare