Login failed for sql authentication

I've developed an ASP2 application. Locally, everything was fine. However, when I deployed it on a remote server. I got this error:

Login failed for user 'domain\userx'.

My connection string is:

connectionString="Data Source=ServerName;Initial Catalog=Aspect;User Id =domain\userx;Password = abc"

I'm quite sure that connection string is fine. I need to mention that whenever I try to connect to the remote server from my client by the Sql authentication, I get the message that login failed for this user. But when I try to connect by windows authentication, it works and there is no error.

I asked the administrator, and he told me that Sql Server authentication is in mixed mode.

So, I really can't figure out what is the problem

From your connection string I found you're trying to explicitly specify a Windows account, which seems to connect with Windows Authentication rather than SQL Authentication. To learn more about Authentication Mode in SQL, please take a look at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_47u6.asp

You can 2 options when connect to SQL, either to specify a SQL account and password; or use WindowsAuthentication by setIntegrated Security to SSPI (true). So if you want to connect with Windows Authentication, firstly you have to add the Windows Account to the SQL logins and grant proper permission to it. More information about creating SQL Logins can be found at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/moddev/html/dehowcreatingsqlserverusers.asp

then write your connection string as following:

connectionString="Data Source=ServerName;Initial Catalog=Aspect; Integrated Security =SSPI;";

This website provides various connection strings:

http://www.connectionstrings.com

|||

Thanks lori_jay

You're right. that was my mistake

However, when I tried to connect with connection string same as you wrote, I got this error "Login failed for user null'. But. I could connect to my DB by using client tools and with windows authentication.

So, the problem occurs when I try to connect through my web application.

Do you have any suggestion ?

|||

Also I have another question.

If I use windows authentication in my connection string. Does everyone in the domain who succesfully logined, have access ?

Thanks.

|||

bkpazand:

However, when I tried to connect with connection string same as you wrote, I got this error "Login failed for user null'. But. I could connect to my DB by using client tools and with windows authentication.

Then is your application deployed under IIS? It seems that other account has been used to connect to SQL. So please make sure you perform proper impersonation when connect under IIS. You can take a look at this post:

http://forums.asp.net/thread/1294820.aspx

And you can also find much information about impersonation on MSDN.

bkpazand:

If I use windows authentication in my connection string. Does everyone in the domain who succesfully logined, have access ?

No. If you want to allow other users in the same domain to connect the SQL Server, you can add the users or usergroup to the local Administrators group of the SQL machine. That's because SQL has a built-in login 'BULITIN\Administrators', all memebers for local admin will be mapped to this login when connect to SQL with Windows Authentication.