We are running Windows SQL Server 2016 Enterprise on Windows Server 2016. The server has an instance with databases in Always On High Availability group. People connect to the instance mostly from MS Access and MS Excel applications and from time to time they experience the following error:
—————————
Microsoft SQL Server Login
—————————
Connection failed:
SQLState: ‘01000’
SQL Server Error: 67
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
Connection failed:
SQLState: ‘08001’
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
There can be a few reasons that cause this error. If you connect SQL Server from MS Office applications and you databases are in Always On availability group:
Make sure you use “MSOLEDBSQL.1” or “ODBC Driver 13 for SQL Server” driver instead of “SQL Server” driver
Instead of connection string like “DRIVER=SQL Server;SERVER=AlwaysOn Availability Group Name\Instance Name;UID=User;PWD=Password;APP=Microsoft Office 2016;WSID=Host;DATABASE=DBName;”
you should have the string like
“DRIVER=ODBC Driver 13 for SQL Server;SERVER=AlwaysOn Availability Group Name\Instance Name;UID=User;PWD=Password;APP=Microsoft Office 2016;DATABASE=DBName;TrustServerCertificate=Yes;MultiSubnetFailover=Yes”
or
“Provider=MSOLEDBSQL.1;Password=Password;Persist Security Info=True;User ID=User;Initial Catalog=DBName;Data Source=AlwaysOn Availability Group Name\Instance Name;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=Host;Initial File Name=””;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False;Application Intent=READWRITE;MultisubnetFailover=True;Use FMTONLY=False”
In this case the error SQL Server does not exist or access denied occurs because “SQL Server” driver cannot work properly with Always On groups. When connection to an instance is initiated AlwaysOn group’s FQDN is resolved to IP addresses of each instance and the driver picks random IP address. If IP of passive instance was chosen then we get the error.
Make sure TCP/IP protocol on SQL server is turned on and SQL Server Browser service on SQL server is running
To assign a TCP/IP port number to the SQL Server Database Engine
- In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for <instance name>, and then double-click TCP/IP.
- In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. (You will probably see both IP version 4 and IP version 6 addresses.) Right-click each address, and then click Properties to identify the IP address that you want to configure.
- If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.
- In the IPn Properties area box, in the TCP Port box, type the port number you want this IP address to listen on, and then click OK. Multiple ports may be specified by separating them with a comma.
Note
If the Listen All setting on the Protocol tab is set to “Yes”, then only TCP Port and TCP Dynamic Port values under the IPAll section will be used and individual IPn sections will be ignored in their entirety. If the Listen All setting is set to “No”, then the TCP Port and TCP Dynamic Port settings under the IPAll section will be ignored and the TCP Port, TCP Dynamic Port, and Enabled settings on the individual IPn sections will be used instead. Each IPn section has an Enabled setting with a default value of “No” which causes SQL Server to ignore this IP address even if it has a port defined.
- In the console pane, click SQL Server Services.
- In the details pane, right-click SQL Server (<instance name>) and then click Restart, to stop and restart SQL Server.
Connecting
After you have configured SQL Server to listen on a specific port, there are three ways to connect to a specific port with a client application:
- Run the SQL Server Browser service on the server to connect to the Database Engine instance by name.
- Create an alias on the client, specifying the port number.
- Program the client to connect using a custom connection string.
Make sure firewall on SQL Server allows incoming connections
Try to ping the instance from a client computer. Open firewall settings on SQL Server and make sure incoming connections to instance’s TCP port (see above) are allowed.
