asked on

Connecting to SQL Server

Have home network, no domain, with SQLServer on one machine, and I code from another.

Can't seem to connect in ASP, but I can connect in .NET.

ASP Error -

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Here's the code:

<%
set conn = server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB.1;User ID=*****;Initial Catalog=****;Data Source=********"
Dim str
str = "INSERT INTO contacts (FNAME, LNAME, Address) Values ('A','A','Nowhere')"
conn.Execute str
conn.close
%>
Have tried Server name and IP Address...

But in ASP.Net, it is fine and insert record properly:

Dim Conn As SqlConnection = New SqlConnection()
Conn.ConnectionString = "Data Source=******;Initial Catalog=******;UID=****;PWD=*****;"
Conn.Open()
Dim cmd As SqlCommand
Dim str = "INSERT INTO contacts (FNAME, LNAME, Address) Values ('A','A','Nowhere')"
cmd = New SqlCommand(str, Conn)
cmd.ExecuteNonQuery()

Any thoughts?

alorentz

ASKER

I have tried these as well:

Conn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=****;Initial Catalog=*****;Data Source=******"

Conn.open "myDSN" 'DSN connects without a problem from Control panel, but not in code....same error as above.

fritz_the_blank

I have successfully connected this way:

      set objConnection=Server.CreateObject("ADODB.Connection")
      'SQL Server Connection String
      'strConnectString = "Driver={SQL Server};Server=SQLServerName;Database=TestData;Uid=sa;Pwd=;"

      objConnection.ConnectionTimeout = 15
      objConnection.CommandTimeout = 10
      objConnection.Mode = 3 'adModeReadWrite
      if objConnection.state = 0 then
            objConnection.Open strConnectString
      end if

BTW--I could use your help here:

https://www.experts-exchange.com/questions/21022953/No-records-returned-with-parameratized-query.html

Fritz the Blank

huji

Well, it may or may not apply, but you have not provided any information for password. You have just provided a username. I wodner if you have to provide a password, even if it is blank.
Wish I can help
huji

fritz_the_blank

Of course, it would help if I uncommented the pertinent line:

set objConnection=Server.CreateObject("ADODB.Connection")
'SQL Server Connection String
strConnectString = "Driver={SQL Server};Server=SQLServerName;Database=TestData;Uid=sa;Pwd=;"

objConnection.ConnectionTimeout = 15
objConnection.CommandTimeout = 10
objConnection.Mode = 3 'adModeReadWrite
if objConnection.state = 0 then
objConnection.Open strConnectString
end if

alorentz

ASKER

No go, still error....

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.

fritz_the_blank

As I am sure you know, 0x80004005 indicates a permissions issue. Are you using windows or password authentication?

FtB

huji

Just have a look at this:
www.able-consulting.com/ADO_Conn.htm
Huji

Carl Tawn

You don't appear to have used the SERVER paramater in your connection string, you have used Data Source which I don't think you need.

Something like the following should work:

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=SQLOLEDB.1;Server=YourSQLServerName;Initial Catalog=NameOfDatabase;UID=UserIDHere;PWD=PasswordHereIfRequired"

Hope this helps.

alorentz

ASKER

>>As I am sure you know, 0x80004005 indicates a permissions issue. Are you using windows or password authentication?

Which is preferred? Server set up for SQL Server and Windows Authentication.

fritz_the_blank

Okay, then I think that I know what may be happening:

When you use windows authentication, the classic ASP pages will try to use the IUSR account to connect to the SQL server. So unless you have configured the SQL server permissions for that, you are out of luck.

To get things going, I would suggest switching to the password authentication for now as you can specify that in your connection string. If that works, then you know for certain where the problem lies and can go about fixing it.

FtB

alorentz

ASKER

I'll try that, but I have roles setup for IUSR on the SQL Server...

Also, not dodging your question, just trying to get this straight first...

fritz_the_blank

It's okay about the other question--I figured it out. (%$#$@^#$% non standard SQL!)

FtB

fritz_the_blank

In any event, I have always found using password authentication to be less problematic when connecting to SQL via ASP.

FtB

alorentz

ASKER

Arghh...no difference....might just say screw ASP, and go all .Net from now on...

fritz_the_blank

What does your connection code look like now?

FtB

alorentz

ASKER

set objConnection=Server.CreateObject("ADODB.Connection")
strConnectString = "Provider=SQLOLEDB; Initial Catalog=*****; Data Source=192.168.0.102; User ID=****; Password=*******;"
objConnection.Open strConnectString

ap_sajith

Try creating a DSN to the relevant SQL server first. See if it works. If it works, then there is obviously something wrong with the connection string. Otherwise there is something wrong with the SQL server setup/ authentication.

StrConn = "Provider=sqloledb;Data Source=xxx.xxx.xxx.xxx; Initial Catalog=<DBName>;UID=<UID>;PWD=<PWD>"

Also, make sure that you have the latest version of MDAC installed. Also, make sure that the sysadmin has not put up any restriction on the connectivity between the two servers.

Cheers!!

Rejojohny

how have u configured ur SQL server to accept connection. Is it windows authetication or SQL authetication or both .. have a look at the "properties" of the SQL server .. use ur enterprise manager for it .. and have a look at the securit tab ...it has a "authetication" section with 2 radio button .. which of them is selected .. use the same type for the connection from the ASP page ...

alorentz

ASKER

@ap_sajith - As stated above, i tried DSN...the DSN connection works, but usage via ASP does not.

@Rejo - Discussed earlier in thread, and it is set to both...

ap_sajith

Have you tried disabling anonymous access to the website? and try logging in using admin ID?.

Cheers!!

fritz_the_blank

This is strange. If I understand what we have above, then the following is true:

1) the authentication is set to password
2) there is a DSN, and it works
3) your connection string looks valid
4) you are still getting a permissions error
5) the same environment works fine under .NET

Does anyone know the difference between the way that ASP classic works differently than does .NET in terms of connection?

FtB

alorentz

ASKER

All of the above are true...

.Net connects and inserts record fine, but needed to set impersonate=true in web.config. ASP does not connect, adn shows error above.

Note, I control both SQL Server (sitting right next to me), and the coding machine (typing into it now)

ap_sajith

Checkout these KB articles..

http://support.microsoft.com/default.aspx?scid=kb;EN-US;176377
http://support.microsoft.com/default.aspx?scid=kb;EN-US;176379
http://support.microsoft.com/default.aspx?scid=kb;EN-US;176380

Also, try using the following connectionstring
StrConn = "Provider=sqloledb;persist security info=false;Data Source=xxx.xxx.xxx.xxx; Initial Catalog=<DBName>;UID=<UID>;PWD=<PWD>"

Cheers!!

ap_sajith

Hi,
Any Updates?. Do you need any further assistance with this?. Please close this question if no further assistance is needed.
If you need help closing this question, please refer to https://www.experts-exchange.com/help.jsp#hs5 on how to close a question.

Cheers!!

alorentz

ASKER

Yeah. nothing worked. Closing...

alorentz

ASKER

Just asking to close the question, thanks.

fritz_the_blank

I am wondering if there isn't some middle ground here. It seems clear that none of the suggestions resolved the issue. Nonetheless, I think that there is some information in this thread that may be of use to others, and perhaps even new to you? If so, perhaps closing the question out by accepting the most helpful comments would be a gesture of appreciation towards those who took the time to try to help?

Fritz the Blank

alorentz

ASKER

Granted, and I understand that point of view.

However, you know that we are not novice when it comes to this, and with that, we should expect answers that are outside of the norm. Just as when you ask a question, it is not an easy question, and you have already covered the basic issues. When an Expert asks a question, it is because it is out of the ordinary, and the standards just aren't cutting it. And as I've seen, most of the questions I ask go unanswered, because they seem to be to difficult.

I honestly don't feel that anything out of the ordinary was offered here. Everything that was posted was standard connection stuff that doesn't work for this issue, and I explained that. There was no research done to find an answer, as all the posts were regular connection information.

Anthony Perkins

modulo,

>>PAQ-ing the question and refunding 500 points<<
This question appears to be still open or is it just me?

ASKER CERTIFIED SOLUTION

modulo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dentab

Question closed I know, and if you are specifying a username and password I would have thought it would use that rather than NT Authentication - however.

If you have authentication on for the Website then the credentials of the web end-user are used rather than the I_USER account.

Probably dont help you - but may prove useful to anyone else reading this.

JohnHoft

Guys,
OK, I know this question is closed but I had to respond b/c this helped me with my similar 'Can't Connect to SQL on home network' situation and may help alorentz (& others) as well if he can be reached some how (perhaps by admin email?). Fritz_the_blank's response of "...0x80004005 indicates a permissions issue..." confirmed a remote suspicion and Huji's suggestion to visit www.able-consulting.com/ADO_Conn.htm were both the answers for me - thank you both Fritz_the_blank AND Huji!

I had setup a SQL server with a named instance and was getting the exact same error message, but didn't realize my connection parameters were 'instance-free' much like alorentz's appear to be above. Specifically, this did NOT work:
Conn.Open "Provider=sqloledb; Data Source=MySqlServer; Initial Catalog=MyDatabase; User Id=MySqluserID; Password=MySqlPwd"

...but this did:
Conn.Open "Provider=sqloledb; Data Source=MySqlServer\NamedInstance; Initial Catalog=MyDatabase; User Id=MySqluserID; Password=MySqlPwd"

Hopefully, this will help alorentz and others. Thank you again Fritz_the_blank AND Huji - wish I had some points to give . . .

fritz_the_blank

Thanks for the post,

FtB