3

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?

dorian harley
  • 51
  • 1
  • 1
  • 4
  • 1
    So you have a local instance of SQLEXPRESS and that database is configured to disallow tcp/ip connection. Further complicating matters, you cannot go into SQL Server Configuration Manager and change the protocols for SQLExpress to enable tcp/ip? https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/enable-or-disable-a-server-network-protocol?view=sql-server-ver15 – billinkc Jan 09 '20 at 21:22
  • Thank you for the post and the link. I was unable to change the protocols for SQLExpress as I do not have the correct administrative rights on my pc. I have since contacted my IT dept who has now enabled the tcp/ip connection. However, I am still unable to select the database even though the tcp/ip connection has now been enabled. The SQL Server Agent and SQL Server Browser are also not running and i am still trying to investigate this further. I will document everything here in the hope I can resolve the issue and leave information for anybody else who faces similar issues in the future. – dorian harley Jan 10 '20 at 16:09
  • As a consultant, I get to walk into environments that vary in how they have desktops configured. I'd have a conversation with your boss that as a developer, you're going to need to have local administrator rights. Otherwise, you're going to spend a lot of time filling out tickets to get one item fixed only to discover you need the next thing unlocked. Sometimes even with admin rights, UAC prompts wreaked havoc with older Windows desktops blocking group membership from being presented. "Boss, we can waste a lot of time fighting permission issues or you can empower me to solve problems" – billinkc Jan 10 '20 at 17:40
  • I agree fully your comment re: local administrator rights. – dorian harley Jan 16 '20 at 10:28
  • Same issue i am facing in Windows 10 machine. Using VS 2017 Professional + SSDT 15.1. Installed "Microsoft OLEDB Driver for SQL Server 18.4". In SSIS package i do not get list of DB's instead getting error "Test connection failed because of an error in initialising provider. Client unable to establish connection. Registry information is corrupt or missing. Make sure the provider is installed and registered correctly." I have local admin rights and also checked TLS 2.0 entries in registry and its active. Tried all options in the accepted answers but still not working. Any idea what can be done? – Mayank Jha Oct 14 '20 at 11:42

3 Answers3

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.

dorian harley
  • 51
  • 1
  • 1
  • 4
  • You aren't the first and you won't be the last to missed the named instance bit. Glad you were to able to solve it – billinkc Jan 16 '20 at 16:55
1

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

Dulan Shaminda Manujaya
  • 11
  • 2
  • Did it work? Let me know if it didnt. I'll try to look into it a bit more. – Dulan Shaminda Manujaya Jan 09 '20 at 21:52
  • Thank you for your post. Unfortunately, right clicking SQL Server Services only provides the options to: Open, View, Refresh, Export List and the Start Function for SQL Server Agent and SQL Server Browser are currently greyed out. I can't post a screen shot but am continuing to look into the issue. – dorian harley Jan 10 '20 at 16:14
1

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)

dominic
  • 11
  • 1