remote sql server login fails

Hello,

I'm about googled out on this one. I did find one post that looked almost like mine but the thread was never finished. I'm sure this problem has been solved and I'm just having trouble finding it. Can someone help me?

I'm running sql server 2000 on a small workgroup network of winxp machines.

If sql server machine is the same as webserver machine, my asp.net app works. If sql server and webserver are different, the app fails because of sql server login failure.

I am able to explore the database remotely through server explorer in VStudio2003. However, when I try to access the database with the asp.net application, the login fails. I have tried several connection strings. Authentication on the server is 'mixed', the server runs in the system account, and the password for sa is "" (blank password).

I've tried (for instance)
Application["DBConnectString"] = "server=machinename;uid=sa;pwd=;database=Resume";
and
Application["DBConnectString"] = "server=machinename;integrated security=true;database=Resume";

When "machinename" is the same as the machine on which the app is running. both of those connection strings work, and the application runs correctly.

When "machinename" is different (that is, sql server on a different machine from the webserver), depending on the connection string I get either
"Login failed for user 'sa'."
or
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."

Obviously VStudio is using some mechanism to add the remote server to it's list of servers and then to allow me manually to explore the databases on that server. Why am I too stoopid to figure it out?

TIA,
Timuse the IP.

or go to www.able-consulting.com, and click on technology and look up how to use connection strings.|||I'm sorry but your reply doesn't make any sense to me. I did try using the IP address and got the same errors. Was your suggestion that I should learn how to use connection strings meant to be sarcastic?

Maybe someone who knows the answer will post.|||no I wasn't being sarcastic. that site is my main resource for connection strings.

also, it's not uid, it's user id. :) my bad. can't believe I missed that. and pwd is password.|||Well, like I said in the original post, the connection strings both work just fine when the sql server and web server are the same, so I don't understand what you meant about learning about connection strings.

Maybe someone who knows the answer will post...|||I've managed to solve half the problem. Apparently the remote server will not allow sa to login over the network when the password for sa is "". I changed it to a non-blank password and now the following works:
Application["DBConnectString"] = "server=machinename;uid=sa;pwd=password;database=Resume";

Please forgive me for having a blank sa password. The workgroup is behind a firewall and the only substantial databases are northwind and pubs, so I figured I was safe. :-P

So, I guess I am more curious than desperate now. I would really rather get the integrated security form of the connection string to work, because I don't like the idea of hard coding passwords into the connection strings. Is integrated security possible in a workgroup setting?

TIA,
Tim|||You should be able to use integrated security.

Your connection string should look like this: "Server=machinename;Database=Resume;Trusted_Connection=True;"

Presumably you are receiving this error with that connection string:
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."

This means that the Microsoft Windows user account that IIS uses to process the request for the web page does not have a corresponding NT Authentication login in the remote SQL Server. Read up on this issue here:PRB: ASP/ODBC/SQL Server Error 0x80040E4D "Login Failed for User '(Null)'". Be sure to read theAuthentication methods for connections to SQL Server in Active Server Pages andINFO: Accessing SQL Server with Integrated Security from ASP links as well.

Terri|||Thank you for thinking about this. I agree with you: I should be able to use integrated security. I'm beginning to think integrated security is a little bit of a fudge with workgroups, however, and that may be my problem.

I had run across those KB articles before but re-read them at your suggestion. I have tried some of the recommendations without success. Some seem out of date, though. Also, the warning that I got in response to the workaround in KB 176379 against using it was so explicitly scary that I undid the change before I even tested it.

Another KB article mentioned a change that would result in the user being required to login when accessing the remote page. I don't think that is a good idea, especially if it happens every time I want to open/re-open a connection to the database, but it shouldn't be necessary at all, since the user is already logged in to Windows and that's what the idea of integrated security is about, right?

I suspect the problem is related to the fact that the network is a workgroup, with no domain controller to authenticate the user in a totally acceptable way. In my situation, for instance, file sharing works between machines as long as the user is defined on all machines with the same name and password, but I have noticed I'm not able to access my own data in the "Documents and Settings" folder of a machine across the network, even though the drive is shared and I have access to other files on the drive and I'm logged in as the same user as the owner of that folder on that machine. Perhaps SQL and/or IIS are having similar glitches.

What I find particularly exasperating is that, without making any changes to any of the servers' settings, VStudio is able to have its way with the databases on the remote machine, via the Studio Server Explorer. Obviously there is some mechanism by which it is sending my credentials, or someone's credentials. I'm just not seeing the secret incantation in any of the info I've looked at so far.

If you have ever gotten this to work for yourself, and as I said I think the problem is to do with the fact that it's a workgroup although I'd be happy to be proved wrong in that regard as long as I get it working eventually, I'd really like to see specifically the solution. And again, it's a WinXP (Pro) workgroup. Some of the KB articles refer to problems with NT4 that were fixed in Win2000. Not clear whether WinXP "improvements" to Win2000 may have made it easier or harder to do what I want to do.

Thanks again for your suggestions.