SSIS OLEDB Connection Manager - Unable to connect to a database (No database names appear)

2

I am trying to create a connection manager in Microsoft SQL Server Data Tools for Visual Studio 2017 (SSDT) for an integration services project. In the Connection Manager:

  1. The Provider is set to: Native OLE DB\SQL Server Naive Client 11.0

  2. The Server name is set to: the name of the local machine

  3. Log on to the server is set to: Windows Authentication

  4. Connect to a database is set to: Select or enter a database name. However no database names appear in the drop down box - the drop down box is blank. I am expecting the name of the database i am working on, including the master database etc to be present.

  5. When I Test Connection, I get an error message which says:

'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.

Allow remote connections has been ticked in SSMS.

The only thing i can see is the SQL Server Agent and SQL Server Browser has stopped / is not running and the TCP/IP Protocols for SQLEXPRESS is set to disabled - I am unable to enable it without a further Access is denied (0x80070005) error.

I have tried to follow all of the guides but cannot progress. Could somebody please offer some further guidance?

sql-server
tcp
ssis
oledb
ssis-connection-manager
asked on Stack Overflow Jan 9, 2020 by dorian harley

2 Answers

2

I have resolved the issue. It was an extremely silly oversight! On installation, the server name in the Visual Studio 17 Connection Manager was listed as: Local MachineName only. In SQL Server, the Server Name was listed as: LocalMachineName\SQLEXPRESS. The Server Name in VS17 has to be exactly the same as SQL Server. As i said, this is a very silly oversight on my part but have documented for future reference.

answered on Stack Overflow Jan 16, 2020 by dorian harley • edited Jan 16, 2020 by dorian harley
1

SQL Server Configuration Manager ==> SQL Server Services(Left sidebar) ==> Right Click and Start all Stopped services


User contributions licensed under CC BY-SA 3.0