SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL Server does not exist or access denied.
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 07/29/2013 :  13:50:00  Show Profile  Reply with Quote
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.

Edited by - astralis on 07/29/2013 14:07:18

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 07/29/2013 :  14:02:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 07/29/2013 14:02:57
Go to Top of Page

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 07/29/2013 :  14:05:42  Show Profile  Reply with Quote
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

USA
37129 Posts

Posted - 07/29/2013 :  14:15:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 07/29/2013 14:15:35
Go to Top of Page

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 07/29/2013 :  14:20:25  Show Profile  Reply with Quote
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.

Edited by - astralis on 07/29/2013 14:20:44
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 07/29/2013 :  14:28:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
37129 Posts

Posted - 07/29/2013 :  14:28:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
59 Posts

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

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 07/29/2013 :  14:40:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
59 Posts

Posted - 07/29/2013 :  14:46:31  Show Profile  Reply with Quote
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

USA
37129 Posts

Posted - 07/29/2013 :  14:52:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 07/29/2013 14:53:03
Go to Top of Page

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 07/29/2013 :  14:58:03  Show Profile  Reply with Quote
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.


Edited by - astralis on 07/29/2013 14:58:22
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 07/29/2013 :  15:01:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 07/29/2013 15:01:58
Go to Top of Page

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 07/29/2013 :  15:16:34  Show Profile  Reply with Quote
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?

Edited by - astralis on 07/29/2013 15:52:24
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 07/29/2013 :  16:10:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
59 Posts

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

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 07/29/2013 :  17:03:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 07/29/2013 17:04:25
Go to Top of Page

astralis
Yak Posting Veteran

USA
59 Posts

Posted - 07/29/2013 :  17:18:30  Show Profile  Reply with Quote
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

USA
37129 Posts

Posted - 07/29/2013 :  18:07:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
59 Posts

Posted - 07/29/2013 :  18:21:15  Show Profile  Reply with Quote
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.


Edited by - astralis on 07/29/2013 18:22:08
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 07/29/2013 :  18:30:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000