Problems when connect sql2005 locally

Question

Hi All,

When I use asp.net 1.1 to connect sql 2005 enterprise server which just enable local access option instead of remote, I got error message to say database server is not avaiable. I use following connection string

user id=abc;password=abc;server=(local);Integrated Security = SSIP;

user id=abc;password=abc;server=.;Integrated Security = SSIP;

Actually, I used default instance, and ASP.Net and database server are in same server. Everything is fine when I change connection string as

user id=abc;password=abc;server=servername;Integrated Security = SSIP;

Answer 1

Frank,

This is a known issue when SS25 does not have TCP on, the client is using MDAC/Everret(.Net1.1) OLEDB provider. For connection sting like (local) or “.”, the connection will fail. 

One of the workaround, if TCP/IP is off, is to use hostname in connection string that will connect you through named pipe provider, which is what you have seen.

 

Thanks,

Answer 2

One more note is that if you can't change the your applications and they use "(local)" and "." in connection string, you can create aliases for "(local)", and "." using tools like cliconfg.exe or SQL Server Configuration Manager.

 

Please also not that the the there is a typo in your connection string, it should be "SSPI" instead of "SSIP".