Accessing MS-SQL7 from IIS 5.0

[opiate]

I created a simple ADO connection:

Dim Conn, ConnStr, strSQLEx

Set Conn = Server.CreateObject("ADODB.Connection")

ConnStr = "DSN=CTS;UID=sa;pwd=fugazi;"
Conn.ConnectionString = ConnStr
Conn.Open

I'm getting the following error....

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'FUGAZI-WIN2K\IUSR_FUGAZI-WIN2K'.
/lessons/CreateID.asp, line 24


Browser Type:
Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)

Page:
POST 0 bytes to /lessons/CreateID.asp

POST Data:

Any ideals why it's trying to login to the SQL Server with the internet account.

thanks

[antonye]

Originally posted by opiate:
Any ideals why it's trying to login to the SQL Server with the internet account.

...because you haven't told it to use the details you've passed across!

I always use DSN-less connections as it's much easier to update them as you don't need access to the web box itself, however this does pose a security risk if someone manages to remotely get into your machine.

A typical connection string would be:

sDSN = "Provider=SQLOLEDB.1;"
sDSN = sDSN & "Persist Security Info=False;"
sDSN = sDSN & "Integrated Security=SSPI;"
sDSN = sDSN & "Initial Catalog={database};"
sDSN = sDSN & "Data Source={server};"
sDSN = sDSN & "User ID={userid};"
sDSN = sDSN & "Password={password};"


Hope that helps,

[opiate]

Nope! I got the same error. I've tried every connectionstring I can think of. I even tried using Visual InterDev's DataEnvironment Object. Still, the same error.

<IMG SRC="smilies/mad.gif" border="0">

[antonye]

Originally posted by opiate:
Nope! I got the same error.

The error sounds like you haven't created the user on the SQL Database... check that as well.

[opiate]

Yes, I have done so. If you look at the original message it show that it's not using the account name that I'm using in my connectionstring. When I specify the username/password in the connectionstring it tries to logon to the SQL Sever with the anonymous internet account.

[antonye]

This is a bit of a weird error - your script is passing across the anonymous user rather than the desired user you provide - but I reckon it must be something to do with the ODBC rather than your script.

Try deleting the ODBC entry and then run the script again to make sure you get an ODBC entry not found error.

Then recreate the ODBC entry (as a system DSN, don't forget!) and try the script again.

[DualP3Guy]

He is looking at SQL server not ODBC connections. I would help you but I am learning SQL now myself. It takes a lot of configuring. I know that it took me a long time to get my users working. You have to give them permission to each particular database inside of SQL. This part was not too easy to do for me. If i get anymore information then I will post it here for you.

[antonye]

Originally posted by DualP3Guy:
He is looking at SQL server not ODBC connections.

He's using ODBC (ie, a DSN) to connect to the SQL database.

This has been my job for the last 6 years so I do know a thing or two about it!

[opiate]

I worked!

I've never had this problem before, weird crap. I've done this millions of times too. I just deleted the System DSN and recreated it. I've configured quite a few SQL Servers in my day....and Oracle.

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=YourDSNName;UID=yourusername;PWD=yourpassword;"
objConn.Open


Does anyone know how to encrypt/decrypt passwords? I'm looking to create a Usertable with Usernames and Passwords for logon security to use with my app.

Thanks for everyones help.

opiate

[antonye]

Originally posted by opiate:
It worked!

I've never had this problem before, weird crap. I've done this millions of times too. I just deleted the System DSN and recreated it.

I've had this before - ODBC does sometime cache the username/password even though it says it doesn't. The data wizard through Excel does it all the time and is a real bitch to change.

As for encrypting, I wrote a Crypto API DLL that allows you to do just that - decode an encoded string to use for username/password.

As you're using an ODBC entry, you should be safe enough leaving the username/password in there because NT encrypts this into the registry for you, so you don't need to do it.

HTH,