SQL Server Database item in VS08 without SQL Server Express.

Question

Hi. I've encountered this problem (while trying to add SQL Server Database (.mdf) file to my asp.net mvc project):

Connections to SQL Server files (.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkId=49251*

I have SQL Server 2008. Does anyone know how to add that file to a project?

Answer 1

(I believe this will work.)

Move the MDF file to the SQL Server box and use Management studio to attach it, thus creating a database in SQL Server. (You will need to create a transaction log file to go with it.)

Then connect to that database as data source.

Full SQL Server cannot use a connection to a database file specified in the connection string.

Answer 2

If you are using SQL Developer, Standard, or any other version aside from Express you need to attach the database to the SQL server before you can use it. In SQL management studio connect to your server, right click on the Databases folder and choose attach, browse to your database and select it (note that you may need to move it to a directory SQL can see - by default SQL runs as Network Service and cannot peer inside C:\Users).

Once you've done that you need to tell ASP.NET that is the database you wish to use. There are a few ways to do this but by fair the easiest is to override the SQL Express database connection by adding the following to your web.config

<connectionStrings>
    <remove name="LocalSqlServer" />
    <add name="LocalSqlServer" connectionString="Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>
</connectionStrings>

Don't forget to give Network Service, or whatever identity you run your application pool as, access to the database in SQL server.