CODEWITHSUNDEEP
sql-servertcpssisoledbssis-connection-manager
dorian harley
dorian harley

Reputation: 51

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

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?

Upvotes: 3

Views: 20701

Answers (3)

dominic
dominic

Reputation: 11

Just substantial scenarios within SQL Server still depend on OLE DB, only when used by SQL Server components, the SQL Native Client 11.0 provider is supported in SQL Server 2012 through 2019

So, if allowed, it is recommended that use new Microsoft OLE DB Driver for SQL Server(MSOLEDBSQL)

Upvotes: 1

dorian harley
dorian harley

Reputation: 51

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.

Upvotes: 2

Dulan Shaminda Manujaya
Dulan Shaminda Manujaya

Reputation: 11

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

Upvotes: 1

Related Questions