Microsoft OLE DB Provider for SQL Server error '80004005'

Question

I have migrated a classic ASP site to a new server and am getting the following error, message.

I have tried different connection strings but none of them work.

I am not even sure if the connection string is the problem

The new server is a Windows 2012 Server, SQL Server 2008 R2 Express machine.

Microsoft OLE DB Provider for SQL Server error '80004005'

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

/scripts/dbcode.asp, line 31

Application("C2M_ConnectionString") = "Provider=SQLNCLI10;Server=(local);Database=mysite_live;Uid=mysitec_Live;Pwd=mypass;"

can it be 32/64 bit incompatibility? please look here:[link](http://www.a2zmenu.com/Blogs/Miscellaneous/Microsoft-OLE-DB-Provider-for-ODBC-Drivers-error-80004005.aspx) — Dalex, Mar 04 '13 at 20:19
@Dalex thanks for the help, I followed the instructions from your link but still no luck. Any other ideas? — Burt, Mar 05 '13 at 01:58
@MikeC. [cmd.ActiveConnection = Application("C2M_ConnectionString")] <- that is what is at line 31. It is just trying to open the connection from the main post above. Any other ideas? — Burt, Mar 05 '13 at 02:00
Just curious, is the old server still running? Does it work on the old server / did it work? If so, what version of IIS was on the old server? Did you migrate SQL Server as well? What was the old SQL Server version it was using? — Mike C., Mar 05 '13 at 14:11
All code worked on old server, nothing has changed bar the connection string. [OLD] - SQL Server 2008 R2, Windows Server 2003, IIS 7 [NEW] - SQL Server 2008 R2, Windows Server 2012, IIS 8 — Burt, Mar 06 '13 at 12:36
Have you looked in your SQL Server Error Logs? What do those logs say? Any login/authorization failures? — JodyT, Mar 09 '13 at 23:06
@Burt Did you ever solve this?, why have you not accepted an answer if that is the case or left the answer yourself? — user692942, Sep 29 '14 at 11:00

score 10 · Answer 1 · answered Feb 28 '13 at 03:27

10

If it is an Express instance, it is most likely not a default instance, but rather a named instance. So you probably meant:

... "Provider=SQLNCLI10;Server=.\SQLEXPRESS; ...
--- instead of just (local) ---^^^^^^^^^^^^

Otherwise you'll need to show us the server properties in SQL Server Configuration Manager on that machine in order for us to be able to tell you how to correct your connection string.

answered Feb 28 '13 at 03:27

Aaron Bertrand

272,866
37
466
490

This would be my guess. If you moved the sqlexpress instance to this server make sure that is is running and that you have the right instance in your server name. – darin Feb 28 '13 at 04:35
1

Good guess but it is the default instance, it has me stumped, think it may be related to SQL Browser or Firewall or something – Burt Feb 28 '13 at 23:15
Tried SQL Server browser and Firewall all settings seem correct. – Burt Mar 05 '13 at 01:54

score 3 · Answer 2 · answered Mar 04 '13 at 23:02

3

As Aaron Bertrand mentioned it would be interesting to have a look at your connection properties (In Sql Server configuration check if the following are enabled Name Pipes and TCP/Ip). Since you're able to connect from SSMS i would ask to check if the Remote connection is allowed on that server Also can you tell is the Sql browser service is running?

here is a link that i keep close to me as a reminder or check list on probable connection issues on SQL Server. Sql Connection Issues And lastly can you try as provider "SQLNCLI" instead of "SQLNCLI10"

answered Mar 04 '13 at 23:02

Raymond A

763
1
12
29

I see no reason why sqlncli shouldn't work, but have you tried sqloledb as your provider? – John Mar 04 '13 at 23:17
I followed the following blog post and everything seems as expected. Named Pipes, TCP/IP and Firewall all configured correctly. SQL Server Browser started. http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx – Burt Mar 05 '13 at 01:53

score 3 · Answer 3 · answered Sep 28 '14 at 16:31

Step-1: Enabling TCP/IP Protocol Start >> All Programs >> Microsoft SQL Server >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Network Configuration >> Protocols for MSSQLSERVER >> right click “TCP/IP” and select “Enable”.

Step-2: change specific machine name in Data Source attributes'value to (local) will resovle the problem ni SQL SERVER 2012.

Ross Bush · Answer 4 · 2013-02-28T03:15:47.047

2

Try pinging the server in your connection string. The server your application resides on should be able to communicate on the port you specify by credentials. If you are developing locally try specifying "localhost". If the server is clustered or you installed as an instance then you need to specify that instance. Also make sure the server is configured for mixed-mode authentication if using sql credentials.

OR Try

Data Source=localhost;Initial Catalog=DBNAME;Persist Security Info=True;User ID=MyUserName; Password=MyPassword;

edited Feb 28 '13 at 03:15

answered Feb 28 '13 at 03:00

Ross Bush

14,648
2
32
55

Thanks for the quick response, this is a live server with other ASP.Net sites running on it that I migrated and they connect to the same SQL Server fine. The SQL server is on the same box so (local) or localhost should work. Any other ideas? – Burt Feb 28 '13 at 03:05
@Burt Any possibility that the user name or password are wrong? While I assume the user name in your connection string is a dummy, just in case it isn't, I notice that your Uid has a "c" instead of an "e" in "MySite". – Ann L. Feb 28 '13 at 03:08
@AnnL.thanks for the help, pretty sure the user name and password are correct (you are right I replaced them to post on here) – Burt Feb 28 '13 at 03:12

score 2 · Answer 5 · answered Feb 28 '13 at 12:00

2

It can be a permission issue , Please check is that server is connecting with same configuration detail from SQL management. other is username / password is wrong.

answered Feb 28 '13 at 12:00

Jinesh Jain

1,232
9
23

Thanks Jinesh, I can connect using the credentials, so I assume this rules out username or password. Any other ideas? – Burt Feb 28 '13 at 22:42
What do you mean "I can connect using the credentials"? From where? SSMS? On the machine? Details, details, details. There's a reason we're all GUESSING, can you guess why? – Aaron Bertrand Mar 01 '13 at 00:56
We have SQL Server Management Studio where we can enter Server,username, Password . we can enter our detail there and can check DB is accessible from that system. – Jinesh Jain Mar 01 '13 at 07:24
Sorry I should have been specific. I can enter the username, password and server specified in the connection string into SQL server management studio as Jinesh suggested. I was able to connect from SQL Server Management Studio – Burt Mar 01 '13 at 23:32
2

You can refer this URL http://www.sqlstrings.com/sql-server-asp-conection.htm in this Provider Name is specified with different criteria you can verify connection string with this – Jinesh Jain Mar 04 '13 at 05:36

score 2 · Answer 6 · edited May 23 '17 at 11:58

2

Here is what I would do:

EDIT: Note that this SO post, a few down, has an interesting method for creating the correct connection string to use.

Open SSMS (Sql Server Management Studio) and copy/paste the username/password. Don't type them, copy/paste. Verify there isn't an issue.
Fire up the code (this is next for me b/c this would be the next easiest thing to do in my case) and step to line 31 to verify that everything is setup properly. Here is some info on how to do this. I understand that this may be impossible for you with this being on production so you might skip this step. If at all possible though, I'd set this up on my local machine and verify that there is no issue connecting locally. If I get this error locally, then I have a better chance at fixing it.
Verify that Provider=SQLNCLI10 is installed on the production server. I would follow this SO post, probably the answer posted by gbn.
You have other working websites? Are any of them classic asp? Even if not, I'd compare the connection string in another site to the one that you are using here. Make sure there are no obvious differences.
Fire up SQL Server Profiler and start tracing. Connect to the site and cause the error then go to profiler and see if it gives you an additional error information.
If all of that fails, I would start going through this.

Sorry I can't just point to something and say, there's the problem!

Good luck!

edited May 23 '17 at 11:58

Community

1
1

answered Mar 05 '13 at 14:08

Mike C.

3,024
2
21
18

No worries Mike, I really appreciate the time you have taken to help. Hopefully I can get to the bottom of it. Will give it a go tonight and hopefully get it sorted. – Burt Mar 06 '13 at 12:36
I forgot about that UDF trick, I used it to generate a connection string but I still get the same error. Pretty sure there is something else at play other than the connection string. – Burt Mar 07 '13 at 00:51
@Burt Were you able to test the .udl connection on the production server itself? – AardVark71 Mar 07 '13 at 12:09
The UDL on the production server connected, but not through the asp page. Makes me wonder if it is the connection string at all. – Burt Mar 07 '13 at 15:48
Could it be a 32-bit / 64-bit thing? I don't know if you have your asp mapped to 32-bit but try testing it on 32-bit by running C:\Windows\syswow64\rundll32.exe "C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll",OpenDSLFile C:\\test.udl – AardVark71 Mar 08 '13 at 13:06

egrunin · Answer 7 · 2013-03-11T15:06:38.243

In line 31:

cmd.ActiveConnection = Application("C2M_ConnectionString")

How are you instantiating cmd?

Rather than the ConnectionString being wrong, maybe cmd is acting differently in the new environment.

Edited to add:

I see that you've gone from IIS 7 to IIS 8. To run Classic ASP sites on IIS 7 required manual changes to server defaults, such as "allow parent paths." Is it possible that some of the needed tweaks didn't get migrated over?

If you're not running with Option Strict On, you should try that - it often reveals the source of subtle problems like this. (Of course, first you'll be forced to declare all your variables, which is very tedious with finished code.)

Set cmd = server.CreateObject ("ADODB.Command") <<< that is how it is being instantiated, not sure what would be different about that though — Burt, Mar 07 '13 at 00:39

score 2 · Answer 8 · answered Mar 08 '13 at 00:23

Have you ever tried SQL Server OLE DB driver connection string:

"Provider=sqloledb;Data Source=(local);Initial Catalog=mysite_live;User Id=mysitec_Live;Password=mypass;"

or ODBC driver:

"Driver={SQL Server};Server=SERVERNAME;Trusted_Connection=no;Database=mysite_live;Uid=mysitec_Live;Pwd=mypass;"

At least this is what I would do if nothing helps. Maybe you will be able to get more useful error information.

score 2 · Answer 9 · answered Mar 08 '13 at 14:27

Could this be a x86/x64 thing?

The following thread seems to indicate that the (local) alias is a 32-bit alias which fails on 64-bit server: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/c701d510-90e5-4dd0-b14f-ca1d694d6615 (note that the error is exacly what you had)

When you were testing the .udl on the server did you test both x86 and x64?

Following the advice from this blogpost (http://blogs.msdn.com/b/farukcelik/archive/2007/12/31/udl-test-on-a-64-bit-machine.aspx) you could test your local udl :

in 64-bit by just double clicking it (acts the same as running "C:\Program Files\Common Files\System\Ole DB\oledb32.dll",OpenDSLFile C:\\test.udl
in 32-bit by double running C:\Windows\syswow64\rundll32.exe "C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll",OpenDSLFile C:\\test.udl

If you can confirm it's a problem with the alias I'd suggest you create a new one by following the guidelines found here: http://msdn.microsoft.com/en-us/library/ms190445(v=sql.105).aspx

score 2 · Answer 10 · edited May 23 '17 at 11:51

Have you tried to use the server IP address instead of the "(local)"? Something like "Server=192.168.1.1;" (clearly you need to use the real IP address of your server)

In case you try to use the server IP address, check in the "SQL-Server configurator" that SQL Server is listening on the IP address you use in your connection. (SQL Server Configurator screenshot)

Other useful thing to check / try:

And check also if the DB is in the default SQL Server instance, or if it is in a named instance.
Do you have checked if the firewall have the TCP/IP rule for opening the port of you SQL Server?
Have you tried to connect to SQL Server using other software that use the TCP/IP connection?

score 2 · Answer 11 · answered Mar 10 '13 at 16:41

The SQL Server Browser service is disabled by default on installation. I'd recommend that you enable and start it. For more information, see this link and the section titled "Using SQL Server Browser" for an explanation of why this might be your problem.

If you don't wish to enable the service, you can enable TCP/IP protocol (it's disabled by default), specify a static port number, and use 127.0.01,<port number> to identify the server.

score 0 · Answer 12 · answered Jun 15 '23 at 17:14

0

You need to enable TCP/IP & Named Pipes in SQL Server Network Configuration.

answered Jun 15 '23 at 17:14

Mr Kyaing

51
1
6

Microsoft OLE DB Provider for SQL Server error '80004005'

12 Answers12

Linked