SQL Server Authentication

[aecordoba]

Hi there! I'm coding a windows application with VS 2005 Express edition. The application has only a DataGrisView to show the Customers table of Northwind database. (Very simple) When I try to add the database source for the DataGridView, and add a new connection in the wizard, I select the Northwind database, then I select "Use SQL Server Authentication" and set the "User Name" and "Password". When I try to "Test Connection", the connection fail with the following message: Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance. If I use "Use Windows Authentication" (Integrated connection and authentication) there is no problem. In the other hand, If I use the "SQL Server Management Studio", I can login with those user name and password in order to see Northwind database. How can I use SQL Server Authentication in the application? Thank you in advance.

-- Adrián Córdoba

[Sam Xavier]

Can you create a new ConnectionString and then use the same in your app. instead of using it at design time. If this still does not work, then post your ConnectionString here. However, please make sure of these three points: 1) You are missing the User Instance=true clause, which tells SQL Express to spin up a separate process for the ASP.NET account, eliminating the need to explicitly grant permission to ASP.NET. Note that you should never grant sysadmin priviledge to the ASP.NET worker process, since that is a very priviledged permission level. 2) You should be using the |DataDirectory| variable in place of the fully-qualified path to the database, for example: |DataDirectory|\<mdf_filename>.MDF 3) MACHINENAME must be local when using SQL Express. Consider using .\SQLExpress for the machine name.

Best Regards, Sam Xavier www.componentone.com

[aecordoba]

OK, Thank you for your answer. I tried by modifying the connection string in this way: connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Archivos de programa\Microsoft SQL Server\MSSQL.1\MSSQL\Data\northwnd.mdf";Persist Security Info=True;User ID=northwind;Password=PASSWORD;Connect Timeout=30;User Instance=True" but I get the same connection error, when I run the application from "Start Debugging" button from Microsoft Visual C# 2005 Express Edition. May be, Express Edition doesn't allow SQL Server Authentications. Anyway, I don't know about SQL Server security, but I think when I deploy the application, I should use an "Application Role". Do you know, where can I read about Application Roles, and how I have to use it? Thank you, again.

-- Adrián Córdoba

[Sam Xavier]

Have you already modified machine.config file appropriately. If no, then please modify it like this: All the providers' default configurations point them to use the connection string named "LocalSqlServer" which is defined in the "machine.config" file which lives in the framework config directory. In this file, you shoud modify your connectionstring like the following in the connection strings section: <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" .../>

Best Regards, Sam Xavier www.componentone.com