connection string for SQL Server Express from 2.0

One problem I hit that I didn’t expect to in playing around with Visual Web Designer Express and SQL Server 2005 Express was the Web Site Administration Tool (WSAT) having a problem connecting to said SQL Server Express instance even after aspnet_regsql had been run fine, and I could see the aspnetdb database and the requisite tables (through SQL Server Management Studio Express).

Since SSMS Express saw things fine, I pulled up my machine.config (in the \WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG dir).  First, I verified that the providers were set correctly (AspNetSqlMembershipProvider, AspNetSqlProfileProvider, etc) and then checked the connection string.  It was still the default, which was:

    <add name=”LocalSqlServer” connectionString=”data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true” providerName=”System.Data.SqlClient” />

Looking at ScottGu’s blog post about changing the configuration to point to a “real” SQL Server instance, I changed it to this, which worked!

    <add name=”LocalSqlServer” connectionString=”data source=jmanning-p4\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=aspnetdb” providerName=”System.Data.SqlClient” />

It’s still a SQL Server Express isntance (as you can see from the “\SQLEXPRESS” part above, which would be dropped if I was pointing at a SQL Server instance).  My database was correctly at Microsoft SQL Server\MSSQL.1\MSSQL\Data\aspnetdb.mdf, so I’m not sure what was wrong about the first, but I’d rather use the latter form anyway, so I’m happy with the change.

One thought on “connection string for SQL Server Express from 2.0

  1. Thanks for the connection string! I looked all over, but this is the only one that worked properly.

    connectionString=”data source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=mydatabase”

Comments are closed.