Connect to SQL Server from ASP.Net application

Hi

I developped an ASP.Net application (with Web Dev Express ) wich attempt to connect to SQL Server Express (installed in the local machine) using Windows authentication.When i launch my application within Web Dev or from IE navigator i receive the following error message : Cannot open the database 'MyDB' login failed for user ServerName\ASPNet.I use SQL Server Management Studio Express to give the user ServerName\AspNet permission to connet to the database but i still receive the same error message.I created a user with strong password within Management Studio with connect permission and attempt to connect from my application using SQL Server authentication i receive the error message : The user 'UserName' is not associated with a trusted connection for SQL Server.Note that when i use the Connect to Database tool in Visual web dev the connection success when i use Windows authentication but fails when i use SQL Server authentication with the same error message above.My OS is WXP Pro SP2

Thanks for any help

Hi,

The message "The user 'UserName' is not associated with a trusted connection for SQL Server." is related to the fact that you are running on WIndows Authentication only. If you want to use SQL Server authentication, you will have to activate that via SQL Server Management Studio, there is an option on the server properties (Make sure you restart the service after changing that setting)

The other problem is that you have to give the user appropiate permissiosn on the database, just giving him connect permissions means that the user will not be able to access the database. He can just connect at server level. You will have to link his server principal to a database principal (by mapping the user to a database user in the appropiate database which has right to gain access to the data). if you don′t have any role to put the user in, you can just create a database user from the server user.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||I am seeing this error too. However I had the system running just fine with asp.net until I installed the microsoft sql server management studio express edition on my machine, now it always comes up with this error. I cannot find what has changed, I have tried to put the aspnet user login into the logins and giving them permission to everything and it still fails. What is the problem here? What is it that the management studio changes that breaks the connectivity?

Thanks,
David.|||

Thanks Jensen for help

I created an account in SQL Server Management Studio with strongly password and i've gived it necessary permissions in the database(connect, read and write) then i configured the server to connect using SQL Server authentication with the account i created , i saved the property and restarted my server like you said.But when i run Mangement Studio again and want to connect to the server using the account above i receive the following error message:

'Cannot connect to MachineName\SQLEXpress

Additionnal informations: A connection was successfully established with the server but then an error occured during the login process(provider:Shared memory, Provider error 0 -There is no process on the other extremity of the pipe. Microsoft SQL Server Error:233'

I d'ont know what this message means.Any idea?

Thanks.

Abdou

|||Hi,

could you please post your connection string in here and indiecate which Data Access components you are using for the connection to the server (wheter you use any old ADO or the new .NET Client)

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hello Jensen

I just resolved the connection problem using SQL Server authentication.The problem was that i did'nt enable the 'Enable SQL Server authentication' checkbox in the property page of the server in 'SQL Management Studio'.Now my application works well and i connect to the server using 'SqlClient' class of NetFrameWork like this:

SqlClient conn = new SqlConnection();

string strConn = @."Data Source=(local)\SQLEXPRESS;User ID=UserName;psw=MyPWd;database=MydataBase;";

conn.ConnectionString = strConn;

Conn.Open(); etc...

Thanks of any suggestion.

Abdou

|||

Let me just add the picture if anybody is not knowing where this is located in the management tools:

http://www.sqlserver2005.de/SharedFiles/RemoteConnection.jpg

-Jens Sü?meyer.