SQL Server authentication
I am trying to take an existing application (asp) I made for MS Access 2000 and convert it to SQL Server. I have VERY little SQL server experience so I appologize if my questions sound basic.
The application lists all databases in an existing folder (*.mdb) and allows the user to open the database showing all tables/queries in that database. This is easy to do using asp and Access but I am having a problem connecting to to the SQL server to list the databases.
Setup:
2 servers -
1 WWW server (MyWWWServer)
1 SQL server (MySQLServer)
I have installed the client software on MyWWWServer for SQL server but it still does not allow me to have access to the SQL server (I administer both servers... scary huh?). I am using Windows & SQL authentication on the SQL server and the client is setup for Use Windows Authentication. The error I get when trying to connect is "SQL Server registration failed because...SQL Server does not exist or access denied. ConnectionOpen(Connect())."
Am I wasting my time or do I need the client installed? Is this what's stopping me from accessing the SQL server?
Thanks for any help.
Peter J. Boettcher posted this at 21:30 — 2nd July 2002.
They have: 812 posts
Joined: Feb 2000
There could be a whole bunch of problems preventing you from connecting to your SQL Server.
First off, can you connect to your SQL Server from your development PC using Enterprise Manager?
You shouldn't have to install any "client" software on your web server unless you're using some kind of specialized application. Just make sure you've got the latest version of ADO installed and everything should be ok.
Are your database and web server on the same network? Remember, by default all SQL transactions go out over port 1433, so if you're trying to connect through a firewall, make sure you have this port open.
PJ | Are we there yet?
pjboettcher.com
artsapimp posted this at 23:13 — 2nd July 2002.
They have: 330 posts
Joined: Apr 2000
Thank you for your reply.
Yes. I have used Enterprise Manager on the local machine to create a testing database.
I thought I was moving in the wrong direction, but it confused me when the client software wouldn't run.
I had SQL server running on the WWW server a long time ago and it worked so I'm sure the ports are not blocked. And yes, I can ping the server.
What is a common connection string to make a connection to the server? I have tried a few and don't really know which one is best. We will be using a UN/PW on our server (call it user1 and pass1 for now).
Thank you for your help.
Free Math Test
Fun Math Games
Peter J. Boettcher posted this at 01:41 — 3rd July 2002.
They have: 812 posts
Joined: Feb 2000
I don't know for sure but I don't think pinging is a good test for checking ports, it just checks if it can see a machine, I don't think it goes over TCP. I remember fighting with one of our tech guys on this, the web server couldn't talk to the SQL server, and he kept saying "It's not my fault, I can ping the SQL Server from the web server!" A couple days later we discovered that the firewall was indeed blocking port 1433 between the two machines, once they opened the port, problem solved
As for connection strings, I like to use OLEDB: "PROVIDER=SQLOLEDB; NETWORK=DBMSSOCN; SERVER=MyDBServer;DATABASE=MyDatabase;UID=MyUsername;PWD=MyPassword;"
I find it faster than using ODBC.
PJ | Are we there yet?
pjboettcher.com
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.