Help! Getting error "Provider cannot be found. It may not be properly installed."

1

We moved our VB6 application's database to another server and changed the connection string of our application to point to it. The following is the connection string and it does not work. It gives the error "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

Provider=SQLOLEDB.1;Password=ppppppp;Persist Security Info=True;User ID=uuuuuuu;Initial Catalog=AppDB;Data Source=10.10.10.10\NEWMSSQL

It seems our application has a problem with the named instance NEWMSSQL because when I try with just Data Source=10.10.10.10, it works, but that's not where the database resides so I can't use that. I thought the provider SQLOLEDB.1 is unable to recognize the named instance so I tried changing the provider to SQLNCLI.1, changed the connection string to the following and it works on my development machine.

Provider=SQLNCLI.1;Password=ppppppp;Persist Security Info=True;User ID=uuuuuuu;Initial Catalog=AppDB;Data Source=10.10.10.10\NEWMSSQL

But when I deploy the application to a user's machine, it gives the error "Provider cannot be found. It may not be properly installed."

Could anyone please shed light on what's wrong and help me fix this problem, please?

Thank you so much in advance for your much appreciated help!

2

Are you using a specific sql user or an AD user?

I think your connection string looks fine
https://www.connectionstrings.com/sql-server/

Provider=sqloledb;Data Source=myServerName\theInstanceName;
Initial Catalog=myDataBase;Integrated Security=SSPI;

3

What is an AD user? I'm using whatever user ID and password is entered by the user who happens to run the application. The user ID and password entered by the user is placed in the connection string to replace "uuuuuuu" and "ppppppp".

4

active directory user. where does your list of users come from?

5

The new instance is a named instance - and it looks like you installed the new instance on the same node as an existing instance.

Verify that the SQL Browser service is up and running on that node - if not, it needs to be running to allow you access to the named instance. If you cannot start up this service, then you need to modify the named instance to use a static port - and then use that port in your connection string instead of the name.

6

Yosiasz, our list of users comes from our Active Directory.

7

do your users that need to have access to db configured properly in new sql server?

8

Thanks for your input Jeff!

Could you please give me the syntax for the connection string that uses the static port?

9

Yes they are configured properly.

10

try this maybe

Data Source=190.190.200.100,1433;Initial Catalog=myDataBase;Integrated Security=SSPI;
User ID=myDomain\myUsername;Password=myPassword;
11

This worked! Thank you so much! :smiley:

I had to assign a static port number (1434) to the named instance, as 1433 was already being used for the main node instance. So I'm using port 1434 to connect to the named instance.

Thanks again!

12

thank jeffw8713

13

Oh yes! Sorry I suddenly got so busy and got back only now.

Thank you so much Jeff! It was actually your idea to assign a static port and that finally solved the problem for me. :smiley:

14

You are welcome - but you really should consider a different static port. 1434 is already utilized and assigned as the UDP port for the SQL Browser service.

15

So, what port number do you suggest? Port 1434 seems to be working fine though. What could possibly go wrong with 1434 that I'm not seeing yet?

16

it might work now maybe bcs sql browser service is stopped but you will get issues when that service is started. use another port as recommended

17

I'm thinking to use port 1438. Is that fine?

18

Using a port in the list of assigned ports could cause issues because those ports are assigned to specific applications. The normal recommendation is to use a port outside the assigned port ranges.

See: https://en.wikipedia.org/wiki/List_of_TCP_and_UDP_port_numbers

19

Thanks Jeff! I think I'll use 1483 as it's not in the list and there is not any 148x number in the list so the 148x range is free. :smiley: