Guaranteed
Free workshop
Commitment in Scrum
28 Oct, 2021 | 16:30-18:00 | Europe [ BST ] Workshop Beginner 90 Minutes

Connecting to SQL Server using DNS

Audience

No items found

Table of Contents

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.

imageimage  image  image
image

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…smile_speedy

 

Technorati Tags:  

Create a conversation around this article

Share on facebook
Share on Facebook
Share on twitter
Share on Twitter
Share on linkedin
Share on Linkdin

Want to learn more?

Check out the many training classes that we have.

No items found

Want to read more?

OUR TEAM

We believe that every company deserves high quality software delivered on a regular cadence that meets its customers needs. Our goal is to help you reduce your cycle time, improve your time to market, and minimise any organisational friction in achieving your goals.

naked Agility Limited is a professional company that offers training, coaching, mentoring, and facilitation to help people and teams evolve, integrate, and continuously improve.

We recognise the positive impact that a happy AND motivated workforce, that has purpose, has on client experience. We help change mindsets towards a people-first culture where everyone encourages others to learn and grow. The resulting divergent thinking leads to many different ideas and opportunities for the success of the organisation.