Online Help > Support/Resources > Troubleshooting > Data Sources > SQL Server

Troubleshooting SQL Server Connectivity

Content

 

New SQL Server installation

Unable to connect to SQL Server

A network-related or instance-specific error occurred...

Universal Data Link (.udl) file

Login failed for user Reason: Token-based server access validation failed

Login failed. The login is from an untrusted domain and cannot be used with windows authentication

 

Issues

 

New SQL Server installation

SQL Server installs with limited network connectivity. Therefore, when initially installed, the Database Engine cannot be accessed from another device.

 

Allow Remote Access

On older versions of SQL Server, remote connections must be allowed manually. Follow these instructions to enable connectivity.

 

Enable protocols

To connect to the Database Engine from another device, a protocol, such as TCP/IP, must be enabled.

 

1.In the SQL Server Configuration Manager, expand SQL Server Network Configuration.
 

2.Select an instance.
 

3.Right-click the desired protocol, then select Enable.

 

Open a communication port in the firewall

To connect to an SQL Server from another device, a communication port must be opened in the firewall.

 

1.In the Start menu, type WF.msc and press Enter to open the Windows Firewall with Advanced Security.
 

2.Select Inbound Rules in the left pane.
 

3.Right-click Inbound Rules, then select New Rule....
 

4.In the Rule Type page, select Port, then click Next.
 

5.In the Protocol and Ports page, select TCP. Select Specific local ports, then type the port number of the instance of the Database Engine. The SQL Server default instance listens on port 1433. Click Next.
 

6.In the Action page, select Allow the connection, then click Next.
 

7.In the Profile page, select the profiles that describe the computer connection environment, then click Next.
 

8.In the Name page, enter a Name and Description for the rule, then click Finish.

 

Unable to connect to SQL Server

1.Go in Help - View Application Log to see if relevant error messages are present. There's a good chance that you will see A network-related or instance-specific error occurred...

 

2.Create a Universal Data Link (.udl) file to test your SQL Connection (see Universal Data Link (.udl) file).

 

3.Anti-virus or Firewall is blocking the application.

 

Test the connection with a Universal Data Link (.udl) file

This will test that the instance is allowing your workstation to connect. Firewalls and anti-virus software may still prevent Remote Desktop Manager from accessing the network even though the Universal Data Link test is conclusive.

 

1.Open the Windows File Explorer.
 

2.Navigate to the folder in which the .udl file will be stored.

 

3.Select the Home tab. Using the New item menu, create a Text Document.

 

4.Rename this file and change its file extension to .udl.

File name extensions might be hidden by the Windows File Explorer.
To show the file name extensions, in the View tab of the File Explorer, enable the File name extensions option.

 

Do not include the space characters in the name of the file.

 

A warning that changing file extensions can cause files to become unusable might be displayed. Disregard it.

 

5.Double-click the .udl file to open the Data Link Properties dialog box.

 

6.In the Provider tab, select the Microsoft OLE DB Provider for ODBC Drivers provider.

 

7.In the Connection tab, select Use connection string, and enter a custom connection string.

Example of valid connection string:

Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

 

8.Use the Advanced tab to view and set other initialization properties for your data.

 

9.Use the All tab to review and edit all OLE DB initialization properties available for your OLE DB provider.
 

10.In the Connection tab, use the Test connection button to test the SQL Server connectivity.

 

11.Click OK to save the connection string to the Universal Data Link (.udl) file.

 

Login failed for user – Reason: Token-based server access validation failed

Finding the true reason for this is quite a process. A web search reveals so many solutions to attempt, and it is easy to get lost is all those steps. The thing to keep in mind is that the error message is misleading and the cause is often a simple thing.

 

The first step is to ensure that the issue is not unique to Remote Desktop Manager. Testing with a Universal Data Link file as shown above will prove that the workstation configuration is not the issue.

 

When granting rights to the DB using AD group membership and one of your user has that error message, you must check privileges for ALL the AD groups that he belongs too. Anything with an explicit DENY must be evaluated carefully. The query below will help identify these occurrences.

 

select princ.*, perm.* from sys.server_principals princ, sys.server_permissions perm

where perm.grantee_principal_id = princ.principal_id

AND state_desc = 'DENY'

 

Login failed. The login is from an untrusted domain and cannot be used with windows authentication.

Error message

 

The following error message appear when you can't access Remote Desktop Manager data source with Integrated Authentication when connected to a Dialup VPN.

 

Solution

1.Locate your VPN connections (.pbk) file.

 

2.You can find it here: %APPDATA%\Microsoft\Network\Connections\Pbk or if you have it set to allow all users to use the connection, you can find it here: C:\ProgramData\Microsoft\Network\Connections\Pbk

 

3.Edit the .pkb file with a text editor and find the line UseRasCredentials=1
 

4.Disable this setting by changing the 1 to 0: UseRasCredentials=0

 

A network-related or instance-specific error occurred...

This error message means that the server could not be reached, the possible causes are numerous.

 

These steps are for Client side troubleshooting exclusively. For full guidance on this issue please consult this article on Technet

 

If the error message indicates that the Named Pipes are in use and you are accessing a remote server, ensure that the TCP IP protocol is used simply by adding tcp: in front of the instance name, i.e. tcp:vmtxg.database.windows.net

 

CAUSE

DIAGNOSTIC

Server name mistyped (Known as the Instance name)

Universal Data Link (.udl) file

Only server name is specified when there is no Default instance on that server.

Universal Data Link (.udl) file

Is the SQL Server up and running.

Universal Data Link (.udl) file

SQL Server is listening on a non-standard port

Universal Data Link (.udl) file

Anti-Virus blocking Remote Desktop Manager

Check in the Anti-Virus logs to see blocked threats.

Firewall is preventing Remote Desktop Manager to connect.

Adjust your firewall to allow RemoteDesktopManager.exe and RemoteDesktopManager64.exe to communicate with external services