SQL Server authentication (asp)

[artsapimp]

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.

[UncaDanno]

To connect to SQL Server: not too hard.
To list the databases and their stored procs: not too easy.

To connect to SQL Server:
You will need to set up a user account for each database.
Are you using SQL Server 7.0 or 2000? There is a big difference.

If using 2000, go to the Security section for the server itself and create a user with a password. In the user's Properties, indicate which databases the user can access as db_owner.
Your connection string will need to include this userID and password in order to connect.

If your're using 7.0, you'll need to go through the user setup for each database on the server.


Problem is, I'm not really sure how you would go about listing the databases and their stored procs, though, because you would need to build queries against the server's system tables AND the databases' system tables.
Don't know of any commercial web apps that would allow an internet user to do such a thing.

[mosquito]

Connecting to SQL Server:

Dim cn 

set cn = createobject("ADODB.connection")

Dim ServerName, DatabaseName, UserName, Password, Trusted 

'Indicate if you want to connect trusted or not
Trusted = false

ServerName = "yourserver"
DatabaseName = "yourdatabase"
UserName = "youruser"
Password = "yourpassword"

cn.Provider = "sqloledb"

cn.Properties("Data Source").Value = ServerName
cn.Properties("Initial Catalog").Value = DatabaseName

If trusted Then
    cn.Properties("Integrated Security").Value = "SSPI"
Else
    cn.Properties("User ID").Value = UserName
    cn.Properties("Password").Value = Password
End If

' Open the database.
cn.Open

List databases:

Select name from master..sysdatabases

to list stored procedures of a database:

select name from ..sysobjects where type = 'P'

I hope this helps a bit