We know that with the default setup of SQL Server (SQL Server 2000/2005/2008), the ‘Default’ instance listens on TCP port 1433. But we have seen that some customers prefer running SQL Server ‘Default’ instance on a TCP Port other than 1433 (a non-default TCP Port) – primarily for security reasons. If a ‘Default’ instance of SQL Server is configured to listen on a non-default TCP port, with out-of-the-box client configuration the connection to SQL Server from an application over TCP/IP may fail.
For example, if we have a ‘Default’ instance running on a non-default TCP Port and we use a connection string like this – “Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=tcp:sql2005” - we may get an error as shown below -
NOTE: We have used the syntax tcp:sql2005 to explicitly specify TCP connection. If we use a syntax “DataSource=SqlServerName”, other enabled protocols like Named Pipes are attempted (depending on client network configuration and protocol order) as well and connection could still be successful with another enabled protocol. But, we will limit our discussion to the scenario where we enforce (or expect) ‘TCP connection’ either via application connection string or client and/or SQL Server network configuration.
Depending on the client data provider (MDAC/SNAC/SqlClient) we may get errors like below (or a variant of these) –
“[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.”
“Login timeout expired”
“[Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections”
“A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections”
The error occurs because, by default, our client network libraries (used in our client data Providers) attempt to connect to TCP Port 1433 for a ‘Default’ instance of SQL Server. It is also worth noting that, unlike a named instance, our client network libraries don’t attempt to discover the TCP Port dynamically using SQL Server browser service for a ‘Default’ instance. The behavior is same for Windows and SQL authentication.
We have a few KB articles (or blogs) that briefly talk about this scenario and necessary steps to make it work, for example –
http://support.microsoft.com/?id=286303
“The network libraries assume either 1433 or the global default port established with the Client Configuration Utility. If a default instance is listening on a port other than the standard 1433 port, you can provide an alias or change the global default port. You can also connect to the instance of SQL Server by using its server name, its FQDN, or its IP address followed by a comma and the port number.”
Also-
http://support.microsoft.com/kb/823938
But we felt that the topic needs further clarification and it may be helpful if we fully understand the implications of each solution/workaround. Moreover, we have seen that each one of the above 3 solutions do not resolve the issue with all the different Microsoft data providers. So, in this blog, we will dig into the available solutions/workarounds from the client application side when we run SQL Server ‘Default’ instance on a non-default TCP Port.
As the KB articles above indicate, we have 3 possible solutions to make the application connectivity work. Let’s dive into each of these–
#1: Include TCP Port number in the connection string –
We can connect to the instance of SQL Server by using its server name, its FQDN, or its IP address followed by a comma and the port number.
For example (if SQL Server ‘default’ instance is running on TCP Port 3000) -
Provider=SQLNCLI10.1;Integrated Security=SSPI;Initial Catalog=MyDatabase;Data Source=SqlServerName,3000; (Server Name example with SNAC 10 OLE DB)
Provider=SQLOLEDB.1;User ID=MyUserName;Password=MyPassword;Initial Catalog=MyDatabase;Data Source=xxx.xxx.xxx.xxx,3000 (IP address example with OLE DB provider for SQL Server)
Persist Security Info=False;User ID=MyUserName;Password=MyPassword;Initial Catalog=MyDatabase;Server=MySqlServer.Mydomain, 3000" (FQDN example with .Net managed provider for SQL Server)
jdbc:sqlserver:// MySqlServer:3000;databaseName= MyDatabase;integratedSecurity=true; (example with MS JDBC driver)
The advantage of this approach is – scope of the change is limited to the application(s) using the connection string. Also, this solution works for all of our data providers. In fact, for the Microsoft JDBC Driver, currently this is the only viable option for a ‘Default’ instance on a non-standard port . On the downside, when an application UI is used to build a connection string for our data providers, the syntax of “ServerName, TcpPortNumber” may not be considered valid by certain applications (purely an application business rule) –one example we can think of is SQL Server replication setup. Also, certain application(s) may ignore the TCP Port number in Server Name field, for example, connection string used in SQL Server database mirroring –
"Data Source=A,PortA;Failover Partner=B, PortB;Initial Catalog=AdventureWorks;Integrated Security=True;"
As described in msdn http://msdn.microsoft.com/en-us/library/cc917680.aspx
“If the connection to the initial principal server succeeds, then the failover partner name will not be used, but the driver will store the failover partner name that it retrieves from the principal server on the client-side cache.”
This means - any TCP Port number specified in the ‘Failover Partner’ is ignored here by the driver/provider and the driver will get the actual Failover Partner Name and TCP port number from the primary Server . For the Microsoft JDBC driver, Port Number is not even valid with Failover Partner (for details, please review the blog http://blogs.msdn.com/psssql/archive/2008/12/31/sql-2005-jdbc-driver-and-database-mirroring.asp... )
Last but not least, to some users, the syntax of ‘ServerName, TcpPortNumber’ doesn’t look clean and they would like to avoid that by all means, if possible J
#2: Create a TCP alias:
Create an alias with the non-default TCP port on the machine where the client application is running. Alias is an alternate name for SQL Server used to make connection which encapsulates the following elements in the connection string –
1. Server Name,
2. Protocol (Tcp/IP or Named Pipes)
3. Tcp Port number or Pipe Name
We can create an alias using SQL Server client network utility (start->run->cliconfg) tool that comes as part of the Windows OS installation.
Alternatively, you can use SQL Server 2005 or 2008 configuration manager to create an alias.
After we create an alias we can just use the alias Name in the application connection string as Server name (instead of actual SQL Server Name)
A few important points worth noting while using an alias –
1. Any alias created with either SQL Server client network utility or SQL Server 2005/2008 configuration manager is stored in the same place in Windows registry, which is –
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
So, either one of these tools will show us the same aliases that are present on the machine. Also, the same aliases are visible to all of our data providers that utilize an alias (MDAC/SNAC/SqlClient).
2. On a 64 bit Windows machine, depending on the bit-ness (32 bit or 64 bit) of our application, we need to use the corresponding 32 bit or 64 bit tool to create an alias. For example, if a 32 bit application is running on a 64 bit machine we need to use either 32 bit cliconfg.exe (present under c:\windows\SysWow64 folder) or SQL Native client configuration (32 bit) in SQL Server 2005/2008 configuration Manager.
The advantage of alias is a clean syntax – we can take advantage of alias in applications that does not accept the syntax of “ServerName, TcpPortNumber”. Another good thing about the alias is – even if we change the alias configuration (Actual SQL Server name or tcp port number) the application does not need any change as long as the alias name is unchanged, a simple restart of the application may be enough. The impact of an alias is limited to those applications on the client machine (where alias is located) that use the alias name (with a specific data provider in the connection string).
NOTE: The Microsoft JDBC driver (all versions) does NOT recognize an alias and alias will not work with this provider. The Reason is – alias is platform dependent on Windows and the JDBC driver is not platform dependent and we can run JDBC driver on Unix and Linux as well.
#3: Change the Global Default Port:
Global default Port is a TCP port setting that our client network libraries use to connect to a ‘Default’ instance of SQL Server. By default, this port is set to 1433 (in line with the installation of SQL Server ‘Default’ instance with the default setup options).
We can change the Global Default Port in the following way -
A. For MDAC Providers ( ODBC driver for SQL Server and OLE DB Provider for SQL Server) and .Net 1.1 SqlClient Provider:
We can use SQL Server client network utility (start->run->cliconfg) tool that comes as part of the Windows OS installation. For example, If SQL Server ‘Default’ instance is listening on a TCP Port 3000, we can change the global default port (for MDAC providers) as shown below -
B. For SQL Server 2005 Native client providers (ODBC driver and OLE DB Provider):
We can use SQL Server 2005 configuration Manager.
Go to SQL Native Client configuration -> Client Protocols -> TCP/IP -> Properties and change the ‘Default Port’ entry
C. Similarly, for SQL Server 2008 (or SNAC10) Native client providers (ODBC driver and OLE DB Provider), we can use SQL Server 2008 configuration manager.
Go to SQL Native Client 10.0 configuration -> Client Protocols -> TCP/IP -> Properties and change the ‘Default Port’ entry
A few important points worth noting while changing the ‘Global Default Port’–
1. Global Default Port setting is provider specific. As shown in the pictures above, MDAC providers (ODBC driver for SQL Server and OLE DB Provider for SQL Server), SNAC9 (SQL Server 2005 Native Client ODBC driver and OLE DB Provider) and SNAC10 (SQL Server 2008 Native Client ODBC driver and OLE DB Provider) have their own setting for ‘Global Default Port’. .Net framework 1.1 managed provider use the same ‘Global default Port’ setting as MDAC providers.
2. On a 64 bit Windows machine, depending on the bit-ness (32 bit or 64 bit) of our application, we need to use the corresponding 32 bit or 64 bit tool to change ‘Global Default Port’. For example, if a 32 bit application is running on a 64 bit machine we need to use either 32 bit cliconfg.exe (present under c:\windows\SysWow64 folder) or SQL Native client configuration (32 bit) in SQL Server 2005/2008 configuration Manager.
3. After the change, we may need to restart the client application.
NOTE: Per our observation, changing ‘Global Default Port’ does not have any effect on the .Net framework 2.0 provider for SQL Server and the Microsoft JDBC driver and changing the ‘Global default Port’ setting may not help resolve connectivity issues while using these providers.
We personally recommend against changing ‘Global Default Port’ on the machine for a particular provider. A few reasons are –
1. The impact is too global. For example, we change the ‘Global Default Port’ setting to 3000 for SNAC10 provider–this means that any application running on this machine and using SNAC10 provider (either ODBC or OLE DB Provider) will assume TCP Port 3000 for connecting to a ‘Default’ SQL server instance.
2. It may not work when we have multiple applications using same providers, but connecting to multiple default instances listening on different TCP Ports. Consider this scenario – we have 2 applications on a machine, one application uses SNAC10 OLE DB provider and needs to connect to a ‘default’ instance running on TCP Port 3000 and another uses SNAC10 ODBC driver and needs to connect to a ‘default’ instance running on TCP Port 3100. In the scenario, ‘Global default Port’ for SNAC10 provider won’t help.
3. .Net 2.0 provider for SQL Server ignores this setting – this is especially important for ASP .net 2.0 applications or other .Net 2.0 applications where we mostly use .Net 2.0 provider for SQL Server.
To sum up our discussion, if you happen to run SQL server default instance on a non-default TCP Port, we recommend that you consider the points discussed in this blog before adopting a solution and in most cases, we prefer using an alias or specifying the TCP Port number in the application connection string.
Author : Azim(MSFT), SQL Developer Technical Lead and Faiz(MSFT), SQL Developer Engineer , Microsoft
Reviewed by : Adam(MSFT) , SQL Escalation Services , Microsoft