Home > IT Answers > SQL Server > Why can't I connect to SQL Server 2005 throug...
MrGeorge
65 pts.
 Why can't I connect to SQL Server 2005 through ODBC ?
SQL Server 2005 connectivity, ASP, ODBC Microsoft Access Driver Login Failed
Hello people,

the situation is this: we have 2 computers, one is for local store (let's call it backend) and the other is a web server (let's call it frontend). Both are PCs running Windows Server 2003 SP2 and both have the SQL Server 2005 Enterprise Edition installed. The webserver is using IIS for ASP Classic pages. The backend PC computer name is dataone.

My need is this: I want to have a database on the backend PC used for local store and access it through ASP Classic on the frontend PC.

What I have done so far: On the backend PC I created a database and called it zkp_database, then a table zkp_users and put some data into it. I got the SQL Server on the frontend PC to connect to the SQL Server on the backend PC. Now I and can manipulate zkp_database from the webserver. On the frontend PC I went Administrative tools->ODBC and mapped zkp_database through the backend's SQL Server fine and well. I used Windows NT authentication. The alias I used is zkpDB.

I have been trying for almost two days now to get a connection string so that ASP on the frontend PC can manipulate the database on the backend PC. Believe me I have tried everything there is among the most popular visited pages with connection string examples on the web!

The ASP errors I get vary with the connection string I am using, in general most of them complain about not finding the right user or the user not being authenticated correctly.

On the backend PC I created a user and a password for zkp_database (I didn't use the sa account), then on the frontend PC I changed the ODBC configuration so that SQL authentication is used -- and at that point the ODBC dialog window simply won't connect!

What am I supposed to do? ... I am at my wits end here.

Software/Hardware used:
Windows Server 2003, SQL Server 2005
ASKED: Jan 19, 2012  3:24 PM GMT
UPDATED: February 28, 2012  11:24:45 AM GMT
RELATED QUESTIONS
Mrdenny
63,630 pts.
 
  Help
 Approved Answer - Chosen by Carlosdl
Your connection string should look something like this.

Provider=SQLNCLI;Server=backend;Database=zkp_database;Uid=myUsername; Pwd=myPassword;


Don't bother with the SQL Alias.

When you use this connection string what is the error message that you are getting?
ANSWERED:  Jan 19, 2012  10:59 PM (GMT)  by Mrdenny   63,630 pts.
 
Other Answers:
Your connection string should look something like this.

Provider=SQLNCLI;Server=backend;Database=zkp_database;Uid=myUsername; Pwd=myPassword;


Don't bother with the SQL Alias.

When you use this connection string what is the error message that you are getting?
Last Wiki Answer Submitted:  Jan 19, 2012  10:59 PM (GMT)  by  Mrdenny   63,630 pts.
To see other answers submitted to the Answer Wiki View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



 

I’m not an SQL Server expert, and I certainly haven’t developed classic ASP pages, but here are my thoughts.

-You shouldn’t need SQL Server installed on the web server
-You shouldn’t need to connect to the database using ODBC.
-Have you configured the database server to accept SQL Server authentication ?

I would try to connect directly to the database, and if that fails I would post the connection string used and the exact error message returned.

Carlosdl
 60,265 pts.
 

Finally I managed to connect to my database on the remote SQL Server through ASP Classic.

But I didn’t use ODBC. My opinion is that connecting to a database on a network share with ODBC is a joke, even Microsoft knowledge base states it is a known problem hence the error 80004005 I am getting.

On the backend I added a user as administrator. I clicked on the object explorer window then the server name, expand security, then logins and added a new user with a password. I chose SQL Server authentication and ticked the option where the user should renew password on next login. Then I closed the SQL Server, re-started it, connected to my server and was prompted to type a new password. I got in the database I just created fine. So far so good there.

On the frontend computer I started SQL Server and in the registered servers window, right-clicked on database engine->new->server registration and found the SQL server on the back-end computer. I chose SQL Authentication and typed the username and password I chose just earlier. Things went OK and I could manipulate the zkp_database through the SQL Server on the webserver.

Now my original aim was to connect and manipulate the database on the web server through ASP Classic.

I provide the code I used for the ASP Classic webpages as a sample. The hardest thing to find and setup was the connection string. The username and password are needed - these are examples.

<%
Set Cn = Server.CreateObject(”ADODB.Connection”)
set rs=server.CreateObject(”adodb.recordset”)
Cn.Open “driver={SQL Server};server=dataone-06f8edd;Database=zkp_database;uid=john;pwd=2310425400;”
strSQL = “select * from zkp_users”
rs.open strSQL, Cn
Do While Not rs.EOF
response.write (rs.Fields.Item(0) & “<br>”)
rs.movenext
loop
Cn.Close
%>

Hope this will be useful to someone else.

@Carlosdl:

thanks for reading this, I can’t guarantee myself if SQL is needed on the web server but the connection string specifies driver={SQL Server}. If the webserver hasn’t got SQL Server on it where is it going to find this driver? The creation and authentication of the user had to be done.

MrGeorge
 65 pts.
 

@MrDenny:

I tried your solution and it works too thanks a lot! I assume SQLNCLI means SQL Native Client Interface or something?

This site:
http://msdn.microsoft.com/en-us/sqlserver/aa937733
states

“The SQL Server Native Client contains the SQL Server ODBC driver and the SQL Server OLE DB provider in one native dynamic link library (DLL) supporting applications using native-code APIs (ODBC, OLE DB and ADO) to Microsoft SQL Server.”

The main point here is that SQLNCLI contains both drivers. So it makes sense to work. It also worked fine with driver={SQL Server} which I found on a webpage about connection strings. This {SQL[space]Server} syntax looked a bit wrong to me and didn’t believe it would work at first… Now I see it is correct. This site states it is used for version 2.56 or earlier of SQL ODBC Driver.

http://msdn.microsoft.com/en-us/library/aa177865%28v=sql.80%29.aspx

I guess Driver={SQL Driver} provides backwards compatibility so that you can connect to SQL Server using another way.

Thanks for your time, I will stick with Provider=SQLNCLI.

MrGeorge
 65 pts.
 

The “Driver={SQL Driver}” is using the older SQL 2000 drivers which are installed on Windows by default. If you want to use the newer SQL drivers you need to specify the native client.

Mrdenny
 63,630 pts.