Can't connect to a db - no names appear in OLEDB CMgr (SSIS)

Programming
, , , ,
1

Issue: Unable to Create Connection Manager in SSDT

I am attempting to create a connection manager in Microsoft SQL Server Data Tools (SSDT) for a Visual Studio 2017 integration services project.

The settings I am using in the Connection Manager are:

  • Provider: Native OLE DB\SQL Server Naive Client 11.0
  • Server name: the name of the local machine
  • Log on to the server: Windows Authentication
  • Connect to a database: Select or enter a database name

When I use the Test Connection feature, I receive an error message:

Test connection failed because of an error in initializing provider. Login timeout expired A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. Named Pipes Provider: Could not open a connection to SQL Server [2]..

I am using SQL Server 2017 and Microsoft SQL Server Management Studio 18. Remote connections have been enabled, however, the SQL Server Agent and SQL Server Browser are not running and the TCP/IP Protocols for SQLEXPRESS is disabled. I am unable to enable this without an Access is denied (0x80070005) error.

I have followed all the guides, yet I am still unable to progress. Could someone please provide me with further guidance?

2

Answer:

The error message suggests that there is an issue with the SQL Server instance and the provider cannot establish a connection. The possible causes of this error are:

  1. The SQL Server instance is not running.
  2. The SQL Server instance is not configured to accept remote connections.
  3. The SQL Server instance is configured to use a specific port number that is not open on the firewall.

To resolve the issue, try the following steps:

  1. Ensure that the SQL Server instance is running.
  2. Ensure that the SQL Server instance is configured to accept remote connections. You can check this by going to the SQL Server Configuration Manager and enabling the TCP/IP protocol and setting the port number to 1433 (default).
  3. Ensure that the Windows Firewall is not blocking the SQL Server port (default 1433).
  4. If the issue persists, try using SQL Server Authentication instead of Windows Authentication and check if that resolves the issue.

If the above steps do not resolve the issue, you may need to consult with a database administrator or SQL Server expert for further assistance.