Management Studio Express breaks database connectivity
Hi, I'm using SQL Express to runDotNetNuke on my own Windows 2003 server. DotNetNuke(DNN) copies a database on to the server from it's installation files and during the installtion process it fills the database with starter data. That process works fine.
But the minute I attach to the database in SQL Server Management Studio Express (MSE), my web app loses connectivity to the database. When browsing to the default.aspx, I see the following message:
Error Installing DotNetNuke
Current Assembly Version: 04.03.05
ERROR: Could not connect to database specified in connectionString for SqlDataProvider
(don't be fooled by the error message, Dot Net Nuke was fully installed and working before I attached to it with Management Studio Express)
Does anyone know the trick to getting the asp.net app talking to the database again? I'm using Windows Authentication. I have verified that Management Studio Express is what is messing up my connection. Any help is appreciated.
I have already become aware that I must disconnect from the database (in Management Studio Express) for the web app to connect properly. I have already tried that. Same error. So far for me, it seems that simply attaching to the database with Management Studio Express breaks my database connectivity.
[1568 byte] By [
datamark] at [2007-12-26]
Although this is not a DotNetnuke forum, I think I can help you (as my site is running DotNetNuke as well :-). Seems that you are using a user attached database rather than a server attached one. If you attach the database to the server you will have to change the connection string as it reflect the reference to a user instance rather than a server instance (although I think that its server instance as a default setting). COuld you please post the specific part of the web.config with the connection string here ? We will try to help you to rewrite the conenction string to fit yours need and environment.
HTH, jens K. Suessmeyer.http://www.sqlserver2005.de
Thank you for your help. I'm not sure how to choose user vs server attachment. That very well might be my problem. I am using the unaltered web.config from the installation zip and have chosen a http install, not a file based. Here is the connection string as you requested. But see below that too, as my question is evolving.
<
connectionStrings><!--
Connection String for SQL Server 2005 Express -->
<add name="SiteSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Database.mdf;" providerName="System.Data.SqlClient" />
<!-- Connection String for SQL Server 2000/2005
<add
name="SiteSqlServer"
connectionString="Server=(local);Database=DotNetNuke;uid=;pwd=;"
providerName="System.Data.SqlClient" />
-->
</connectionStrings>
<appSettings>
<!-- Connection String for SQL Server 2005 Express - kept for backwards compatability - legacy modules -->
<add key="SiteSqlServer" value="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Database.mdf;" />
<!-- Connection String for SQL Server 2000/2005 - kept for backwards compatability - legacy modulesSince my last post, I have deleted all the previous DNN files and used the startup kit for the first time to reinstall DNN fresh. That worked painlessly. :-) (Even bad programmers can have a good day I guess). So now my question might be different a little. I want to be able to open the database in SQL Server Management Studio, I am afraid to do it after what happened the last 3 times (yes, 3). My process is to open SQL Server Management Studio, and attach to the database. Should I be doing that differently?
And I realize this might be out of scope, but I still have my first successful database copied elsewhere. I worked on it for 3 days before I fatefully opened it in SQL Server Management Studio and it stopped working. It has 60 or so pages that I created. Is there a way to start using that database again, or perhaps back it up and restore it into the new, empty database I have now (and working)?
Hi,
no panic :-)
THe setting here:
<add name="SiteSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Database.mdf;" providerName="System.Data.SqlClient" />defines a user instance (AttachDBFilename=|DataDirectory|Database.mdf;"). A user defined instance needs exclusive access to the file. If you attached the files to the server, the file is under control of the server unless you detach the file or shut down the service. If you want to let the SQL Server Express instance control the database as a server instance, you should use the following connecting string (assuming that you attached the database with the name XXX)
<add name="SiteSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;Initial Catalog=XXX" providerName="System.Data.SqlClient" />Try that, if you have any problems, do not hesitate to come back :-)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Once again I thank you for your time and help. I have tried your connection string and didn't get it to work. Here is what it is now:
<add name="SiteSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;Initial Catalog=Database.mdf" providerName="System.Data.SqlClient" />
I have also tried
<add name="SiteSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;Initial Catalog=|DataDirectory|Database.mdf" providerName="System.Data.SqlClient" />
What am I doing wrong? Do I need to type in the path to the database?
Do I need to alter the appsettings connection string likewise? (I did try that just now, don't know if I should have)
Thanks!
Mark
Jens, That did the trick for a few minutes at least. Then it wouldn't connect again. I see the distinction now between database name and database file name you were trying to convey earlier.
After your help from your last post, it kept telling me there was a problem with the string for the SQLDataProvider. I was unable to figure it out. It is very frustrating.
At this point, I don't know why it worked now, but I restored the original connection strings and placed the original, non-working database back in place and now it connects. I realize I probably still have the original problem of breaking the connection the next time I open the database with SQL Server Management Studio. On top of all that, the website is functioning and all my pages are back - but it will not let me log in. Now I will return to the DotNetNuke forum for tips on breaking in when it won't let you log in. Do you have this much trouble with DNN? I hope not.
Thank you Jens - Mark
