Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL Server does not exist or access denied.

Author  Topic 

astralis
Yak Posting Veteran

62 Posts

Posted - 2013-07-29 : 13:50:00
I have troubleshooted this for a while so I don't understand it.

This 2008 Express DB has not changed for a while, then suddenly I am receiving this error and can't figure out what is wrong.

I can still access the site via Management Studio, though, using the same username and password.

Microsoft OLE DB Provider for SQL Server error '80004005'

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

/inc_db_setup.asp, line 10

Line 10 is the last line (fixed for clarity):

strBFCon = "Provider=SQLOLEDB;Data Source=REDACTED IP,PORT;database=redacted;uid=redacted;pwd=redacted;"
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.connectionstring = strBFCon
adoCon.Open 'LINE 10

1. The Firewall has not changed.
2. TCP/IP and Named Pipes are enabled.
3. The SQL Server events do not point to any issues.
4. Windows events log does not point to any issues.

Please advise.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-29 : 14:02:45
Data Source should be ServerName,Port if you are specifying the SQL listening port in there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

astralis
Yak Posting Veteran

62 Posts

Posted - 2013-07-29 : 14:05:42
Yes, that's what I've been doing. I've used this same connection string for years, two years on the same machine, without a problem.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-29 : 14:15:08
Well try changing it to a comma. You might have received an update through Windows Update that affected this. Maybe an older driver auto-corrected it for you, but now it doesn't work. Try the comma and let us know.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

astralis
Yak Posting Veteran

62 Posts

Posted - 2013-07-29 : 14:20:25
Sorry, I've always used the comma in the connection string. I didn't mean to put a slash in my post. I corrected it in the post.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-29 : 14:28:21
Are you sure it didn't switch ports? Check the SQL Server Error Log to see what port it is listening on. It'll be in the messages at startup.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-29 : 14:28:59
Does IP,Port works in SSMS? Or are you using IP\InstanceName in SSMS?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

astralis
Yak Posting Veteran

62 Posts

Posted - 2013-07-29 : 14:37:51
Yes, the IP and Port works in Management Studio remotely and locally.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-29 : 14:40:37
Try specifying tcp in the Data Source: tcp:IP,Port

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

astralis
Yak Posting Veteran

62 Posts

Posted - 2013-07-29 : 14:46:31
Tanya, I don't know what tcp is.

Are you saying to post it like this? "data source =TCP:10.10.10.10,19181"

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-29 : 14:52:28
It's telling it which protocol to use. It's probably defaulting to named pipes, and we want it to use TCP instead.

Yes just like that, though not sure if it's case sensitive. I always specify tcp in lower case. You can also first test the connection with tcp in SSMS. It accepts the same type of parameters.

~Tara (not Tanya)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

astralis
Yak Posting Veteran

62 Posts

Posted - 2013-07-29 : 14:58:03
Tara, that worked in SSMS but not on the site. I'm still receiving the same error.

My good friend's name is Tanya so my mind typed without my control.

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-29 : 15:01:11
I'm leaning towards this being a database driver issue, possibly something is corrupted with SQLOLEDB. To see if that's the case, we need to do a UDL test. Instructions are here: http://wiki.answers.com/Q/How_to_set_up_an_OLE_DB_connection. Make sure the UDL test is using the same connection string as the site.

The reason why I think this is the case is that SSMS is using a different driver, as far as I know. I believe SSMS uses the native SQL client. Your site is using OLE DB. So we need to test OLE DB.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

astralis
Yak Posting Veteran

62 Posts

Posted - 2013-07-29 : 15:16:34
I tested this on my connection that is remote from the SQL Server. Is this correct?

This is inside the udl file:

Provider=SQLOLEDB.1;Persist Security Info=False;User ID=redacted;Initial Catalog=redacted;Data Source=redacted ip,redacted port

I was able to successfully, remotely connect and view the tables via the udl test.

But when I plugged this into the connection string of the site,(along with the password) I received the same error.

I guess this is pointing to a corrupt SQLOLEDB file? Or is this a permissions issue?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-29 : 16:10:03
Try the test locally too.

It is NOT a permission issue. The error is very misleading.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

astralis
Yak Posting Veteran

62 Posts

Posted - 2013-07-29 : 16:56:10
I tried it locally with no issues.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-29 : 17:03:35
Try the local test again but specify SQLOLEDB instead of SQLOLEDB.1. The .1 was in the link I provided but is not in your connection string.

So you tried the .1 thing for your site already?

I'm really out of ideas. Something is amiss.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

astralis
Yak Posting Veteran

62 Posts

Posted - 2013-07-29 : 17:18:30
I tried the .1 on the site and it didn't work.

I tried testing locally with and without it, too.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-29 : 18:07:56
You've rebooted, right? Sometimes even an iisreset isn't enough.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

astralis
Yak Posting Veteran

62 Posts

Posted - 2013-07-29 : 18:21:15
Just to confirm: how do you want me to test?

1. Test connection via UDL file:
I can't edit the string using this, right? I tested it remotely and locally and obtained the list of tables.

2. Test connection via ASP file:
This fails locally and remotely.

3. Test connection via SSMS:
Success remotely and locally.

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-29 : 18:30:58
All of that is correct. If the UDL test works locally, then I don't think this is a database driver issue. I would think something is wrong with the app's config, but this is outside of my expertise at this point.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
    Next Page

- Advertisement -