Freigeben über

IPv6 and SQL Server 2005

  • Artikel

Starting from SQL Server 2005 (Yukon), SQL Network Interface (SNI), the new SQL Server network library, supports internet protocol version 6, IPv6, through Windows IPv6 stack. As you might already know, currently, many Window platforms can be configured to have IPv4/IPv6 dual-stack. IPv6 only stack will be supported on windows Vista. In any of these configurations, SNI can automatically recognize the existence of IPv6. The configuration required for DBA, database application developer of SQL Server 2005 is very minimal. In most cases, after enabling remote TCP/IP network access, you are good to go with default network configuration of SQL Server 2005 on an IPv6 enabled machine. That being said, there are few things worth mentioning.

Server Side

On a SQL Server 2005 hosting machine that has IPv4/IPv6 dual-stack, if TCP/IP protocol is enabled and configured to “ListenOnAllIPs”(the default setting), the new network libraries (SNI) listens on both IPv4 and IPv6 addresses. In this configuration, SQL Server can accept both IPv4 and IPv6 connection requests. With multi-home feature introduced in SQL Server 2005, SNI allows SQL Server to be configured to listen on individual IP addresses. On a dual-stack machine, one twist is that you can configure SQL Server to listen only on IPv6 addresses and disable all IPv4 addresses so that only IPv6 connection can be accepted. This configuration can be realized through SQL Server 2005 configuration manager.

Client Side

In most cases, the connection string does not need to be modified if the <servername> is specified using server hostname or FQDN (Full Qualified Domain Name). If the server machine has dual-stack, its hostname or FQDN will be resolved into multiple IP addresses, including at lease one IPv4 address and multiple IPv6 addresses. And SNI will attempt to establish connections using these IP addresses in order and use the first connection that succeeds. IPv4 addresses are attempted first if both IPv4 and IPv6 addresses are present. This logic is transparent to the users of ODBC, OLEDB or ADO.NET.

One caveat of this behavior is that if SQL server is configured to listen only on IPv6 addresses on a dual-stack machine as mentioned above, client connection attempts using IPv4 address will fail and it may take longer than default timeout, contributed by default TCP timeout logic during connection establishment handshakes. Even though the subsequent IPv6 connection can succeed immediately, the connection may still fail. A typical error message will be, when SQL Native Client provider is used, “[SQL Native Client]Unable to complete login process due to delay in opening server connection”. The workaround is to specify the IPv6 address of the target machine explicitly to force SNI to use IPv6 connection directly or to use longer timeout value, says 30 second.

If <servername> is specified in IPv4 addresses, SNI will only use IPv4 to make connection attempts. Specifying IPv6 address as the <servername> in connection string can guarantee that only IPv6 is used.

To verify the connection indeed goes through IPv6, the following SQL Server DMV query can help you.
1> select client_net_address from sys.dm_exec_connections where <session_id=@@spid>

2> go
client_net_address
----------------------------------------

2002:9d37:7a38:4:20d:56ff:fefc:b59e <<<<< This is the IPv6 address of the client machine
(1 row affected)

Note that the IPv6 local loop-back address is “::1” while IPv4 loop-back address is “127.0.0.1”.

For more information on IPv6 support on Windows and how to configure IPv6 stack, there is a useful link, https://www.microsoft.com/windowsserver2003/technologies/ipv6/default.mspx. IPv6 stack is available out of box on many Windows platforms, including Windows Server 2003, Windows XP with Service Pack 1, Windows XP with Service Pack 2.

Do you know that you can post question w.r.t SQL Server data access, connectivty issues at https://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1 ?

Nan Tu

Software Design Engineer, SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Comments

  • Anonymous
    August 01, 2008
    You used to be able to connect to SQL Server with IPv4 by using the SQL Server name in this format: LOCALHOST<Instance Name>. Can you do the equivalent of this with IPv6 installed, and if so, how do you do it? LOCALHOST<Instance Name> is not working with SQL Server Express with IPv6 installed in Windows XP.

  • Anonymous
    August 01, 2008
    Do you have the TCP/IP connection method enabled via the SQL Server Configuration Manager (SSCM)?  By default, SQL Express does not enable TCP/IP upon installation. If you want to connect to a local instance and you have TCP/IP enabled, you can connect using:    .MSSQL$SQLEXPRESS where "localhost" has been replaced with a '.'.

  • Anonymous
    July 02, 2009
    Hi I am connecting to my database server using CNAME . However in sys.dm_exec_connections i see REAL SERVER name instead of CNAME . IS there any way i can find that from sql server dmv or any other format.