DNS SRV Record Support for SQL Connectivity
PROBLEM DESCRIPTION
===================
You may want to use DNS SRV record for your global name to resolve to an SQL server instance SERVERX\INSTANCE1. In internet I saw some sites are talking about it was possible to achieve this using a DNS SRV record which points to both the IP address of the server and the port of the instance.
In order to connect to sql server with fqdn, you don't need to create SRV record. SRV record is not in use in this scenario. You can always connect to sql server by using:
-IP address
-Netbios name
-FQDN
-Alias
Sql server doesn't support DNS SRV records directly. You can always query SRV record, parse result and pass this information to your connection string. As you may guess, you can apply algorithm to your applications however this is not possible for management studio
We have to solutions here:
1- You can use client aliases and you can deploy them with logon script
2- You need to change your code which queries DNS SRV record ,parse ip address & port number and build up your own connection string.In my sample ( I know it is not the best way of doing this ), I have used nslookup and specified parameters to return DNS SRV record (nslookup -type=SRV _sql. _tcp.kagan.com) Here are steps to create DNS SRV record and parse it from your custom application:
On DNS Server:
-Click your domain.com under Forward Lookup Zones
-Right Click ->Other New Records->Service Location (SRV)
-Service Name =_sql
-Protocol=_tcp
-Port Number: Specify your sql server's port number
-Enter your fqdn and Close this window.
-Here is sample code which executes nslookup and parses results.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.IO;
using System.Data.SqlClient;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
try
{
/*Create new process to run nslook up to get details of SRV record*/
Process ps = new Process();
ps.StartInfo.FileName = "nslookup";
ps.StartInfo.UseShellExecute = false;
ps.StartInfo.RedirectStandardOutput = true;
ps.StartInfo.RedirectStandardError = true;
/*specifying command line arguments
* -type is used to specify type of query
* _sql._tcp.kagan.com : this is query value
*/
ps.StartInfo.Arguments = "-type=SRV _sql._tcp.kagan.com";
/*start processs*/
ps.Start();
/*get query output from nslookup*/
string output = ps.StandardOutput.ReadToEnd();
Console.WriteLine(output);
/*Now rest of it is string concatination
*search for port keyword
*/
int i = 0;
string port = "";
string internet_address = "";
i=output.IndexOf("port");
if (i > 0)
{
port = output.Substring(i, output.IndexOf("\r\n", i) - i);
port = port.Substring(port.IndexOf('=') + 2);
Console.WriteLine("p:<" + port + ">");
}
/*
*search for internet address keyword
*/
i = output.IndexOf("internet address");
if (i > 0)
{
internet_address = output.Substring(i, output.IndexOf("\r\n", i) - i);
internet_address = internet_address.Substring(port.IndexOf('=') + 20);
Console.WriteLine("ia:<" + internet_address + ">");
}
ps.WaitForExit();
ps.Close();
if (internet_address != "" && port !="")
{
SqlConnection cn = new SqlConnection();
cn.ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ReportServer$SQL2005;Data Source=tcp:" + internet_address + "," + port;
cn.Open();
Console.WriteLine("connection Opened");
cn.Close();
Console.WriteLine("connection Closed");
}
Console.WriteLine("Program Ends");
Console.ReadLine();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
}
}
}
If you would like to write your own code with API's, you can use DNSQuery function to do that. Unfortunately I don't have any sample in hand about how to getDNS SRV records by using APIs however In following link you can find more about DNS query and there is one example posted on this article.
DnsQuery Function
https://msdn.microsoft.com/en-us/library/ms682016(VS.85).aspx
1) how we might query the SQL Browser service directly and
Unfortunately We don't have SQLBrowser service api available. However there are some libraries (System.Data.Sql) which can give information about servername,instance name,IsClustered,Version)
Enumerating Instances of SQL Server (ADO.NET)
https://msdn.microsoft.com/en-us/library/a6t1z9x2.aspx
SQL Server Features and ADO.NET
https://msdn.microsoft.com/en-us/library/cc668761.aspx
2) whether Microsoft are prepared to support DNS SRV records in the future
As I mentioned unfortunately we don't support DNS Srv records for now. Our SQL Server client drivers do not attempt to look for or resolve DNS SRV records in order to locate the SQL Server service port. We have a separate service called Sql Browser service that is used to report the port number to the client. Hence you can create DNS SRV records but the clients will not attempt to use them. Clients accept the host name of the target and then attempt to connect to UDP port 1434 on the target host name to contact SQL Browser service to get the port number for the instance. However Good news is, we planning to incorporating DNS Srv records in our drivers in future releases
Please let me know if you have any questions.
Kagan Arca
Comments
Anonymous
July 14, 2009
i have tried this but nslookup still states that it cannot find address for server _sql._tcp.OURFQDN also is there a way to implement this without have to recode our connection stringsAnonymous
July 30, 2009
The comment has been removed