Archive
Monthly
Go
|
|
DNN Blog
Nov
28
Posted by:
smehaffie
11/28/2006 12:00 AM
Recently I rebuilt my laptop and decided to only install SQL Express and not the full SQL Server 2005. The issue I can across occurs when you attach to the DNN mdb file within SQL Server Management Studio. Once you have done that the connection string does now work and you DNN site cannot connect to the database. And no matter what you do you cannot correct it even by detaching it, restart SQL Express, resetting IIS, etc. I finally had to scrap the database and do it all again. Below is the way to setup DNN and SQL Express if you want to also access the database from within SQL Server Management Studio.
1) Rename the Database.mdb to another name. (This is optional).
2) Open SQL Server Management Studio and do the following
- Right click on the database folder and choose attach.
- Click Add button and point to the database you want to attach to.
- Click on the "Attach As" column and give it a more friendly name. For this blog purpose we will change it to DotNetNuke_ModDev
- In the lower screen delete the line that references the ".ldf" file. This will be created and you will get an error that it cannot be found if you do not delete it.
- Click on the "Current File Path" for the ".mdf" file and update it so it points to the location of the DNN database you are attaching to.
- Click Okay and the database should be attached correctly.
- Set up permissions
- Click on the security\logins folder.
- If the ASPNet user exist you can skip this section.
- Right click login folder and choose "New Login"
- Click the "Search" button, then the "Advance" button, and then the "Find Now". This will bring up a list of user on the computer and select the ASPNet account.
- Make sure Windows authentication is checked and click "OK" to add user.
- Click on "Databases\Security\Users folder.
- If the ASP.Net user exist you can skip this section
- Right click the user folder and choose "New User"
- Click on the "..." button next to the Login Name, then click on the "Browse" button.
- Check the ASPNet user and click "OK" button. Click the OK button on the Select Login screen and this will add the user.
- Under database role membership check one of the following combinations. Option #2 is more secure. Click "OK" button when done.
- Permission Options #1
- Permission Options #2
- db_datareader
- db_datewriter
- db_ddladmin
- db_securityadmin
- Setting Database Permissions (Only needed it Option #2 is chosen above)
- Right click on the database you attached to and choose "Properties"
- Click on "Permissions" under select a page (upper-left of screen).
- Under Explicit permission for {Database Name}, make sure that the Grant box is checked for Execute.
3) Update the connection strings in the DNN web.config file. Do not forget to use the same connection string in the <ConnectionString> and <AppSettings> section.
FROM
Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Database.mdf;
TO
Data Source=.\SQLExpress;Integrated Security=True;User Instance=False;Database=DotNetNuke_ModDev;
4) Access the DNN web site to run the installation process.
You should now have a working DNN site and a SQL Express database that you can access/manipulate using SQL Server Management Studio and still be able to access it from the web site as well.
39 comment(s) so far...
Re: DNN / SQLExpress / SQL Server Management Studio
Yesterday I had same problem. Thanks for this workaround, I will try it :-)
By plachow on
11/29/2006 5:54 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Thanks for the solution to this set-up problem.
I ran into it and decided to use the visual studio browser to get by.
It will be nice to get ('the becomer more') familiar/conststent interface.
The blogs are a great form of support for the DNN Community.
Thanks!
By jbonnie on
11/29/2006 5:54 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
How about for full SQL 2005 servers? whats the syntax of the connection string?
By rexarby on
1/7/2007 12:01 AM
|
Re: DNN / SQLExpress / SQL Server Management Studio
OH MY GOD! YOU HAVE NO IDEA HOW MUCH YOU JUST SAVED MY ASS!
THANK THE HEAVENS!
You sir, are a good and kind person, one day people will write songs about you!
I owe you big time!
By binaryjunky on
1/7/2007 12:02 AM
|
Re: DNN / SQLExpress / SQL Server Management Studio
I'm still getting a db connection error when I run my dnn site, but I can connect with VS 2005 Server Explorer. When I renamed the "Attach As" string for the database I attach to, I dropped the .mdf extension there and in the connection string. Was that correct? And I assumed I needed to detach from SQL Server Management Studio Express before I run dnn (although I tried both ways) because of limits on # of connections.
By msbcgsch on
1/7/2007 12:03 AM
|
Re: DNN / SQLExpress / SQL Server Management Studio
hello - does it matter what name you give the user you create?
Could you explain how the user login gets used?
By kirstengreed on
1/7/2007 12:03 AM
|
Re: DNN / SQLExpress / SQL Server Management Studio
None of this is needed. You can connect to a SQLExpress database just like you do a SQL server instance.
Follow the steps in the doc's, then through SQL Server Management Studio, go to your SQL Express instance and right click, choose properties.
On the left pane, select Security. Make sure Server Authentication is set to Sql Server and Windows Authentication Mode (the default is Windows Authentication Mode).
Once you have that all set, you can set your DB connection strings as you do for any remote DB server using the top entires in both areas.
Contact me via: edegagne@verizon.net if you have any questions.
By edegagne on
1/7/2007 12:04 AM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Here is my response to some of the latest post to this blog.
edegagne, You are correct, but as soon as you access the database from SQL Server Management Studio you get cannot attach to the database from your web site if you do not follow the example above.
rexarby: You can use the same format for a SQL Server connection string as well.
Ikirstengreed: If you are using the Windows Authentication then you have to give the ASPNet user access to the database.
msbcgsch: When you attach the mdf file you rename it to a more user friendly name (in my example DotNetNuke_ModDev) and then that is what you use in your connection string as the Database Name.
By smehaffie on
1/7/2007 12:13 AM
|
Re: DNN / SQLExpress / SQL Server Management Studio
I recently installed DNN on windows 2003 server with sql 2005 express. Took days to get it, but it works and I am including user name & password in the connection string.
I'm interested to read from your note that I should be able to use your connection string at the top of this blog and it will still work?
How is this possible? How can it access the db with authenticating UID & PWD?
Regards,
Graham.
By holiam on
1/9/2007 1:16 AM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Graham,
What do you mean "authenticating UID & PWD"?
If you mean ASPNet (Win2000/XP) or NetworkServices(Win2003) I am pretty sure ASP.Net use the appPool user to access database resources. Therefore if you add the ASPNet or the NetwroServices account to have the appropriate access the site should work fine.
If you mean you site is using windows authentication instead of anonymous access then this would be different. You would need to add the "Authenticated Users" or the "Users" group to have access to the SQL server database. Or you could also create a network group, add the users who need access to DNN, and then give that group access to database the same way.
custom group: Very secure and only select people can access site / database.
Authorized Users: Less secure but only people who have authenticated on your network have access to the site / database.
User: Lest secure. anyone who get access to your network has access to the site and database.
I have not tested out the the Windows Authentication scenerios, but they should work. I use the custom group scenerio on other non-DNN/SQL based projects all the time.
By smehaffie on
1/9/2007 1:24 AM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Graham,
One additional note: For the original instructions and the instructions for a DNN site that uses windows authentication to work the database must be setup for Windows Authentication or SQL Authentication. If only setup for SQL Authentication then neither example will work.
By smehaffie on
1/9/2007 1:27 AM
|
Re: DNN / SQLExpress / SQL Server Management Studio
I am not sure how all this works, but I have not had any problems with SQL Server Express 2005. I use my control panel, DotNetPanel, to auto install DNN 4.3.7 and it adds in these connection strings.
and this one as well.
and it all seems to work well.
I also moved all of my dbs fromWH4L on SQL Server 2000 to SQL Server 2005 Express and just created a SQL Server Express 2005 DB and restored the SQL Server 2000 backup file to it.
Now I need to upgrade a DNN 3.3.5 site to DNN 4.3.7 and I am not sure if I should just emulate the connection strings that are currently working on my other DNN 4.3.7 installs or if I need to try to figure out these SQL Server Express connection strings.
I don't have any issues connecting to all of my dbs in Mgmt Studio.
Any ideas.
By chammock on
1/11/2007 1:32 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Hi!
I found this post and thought it could help me. I need to restore a DNN3 site with SQLExpress on my own computer. This is due to my production site is running on SQL server 2005 on my webhost, and I don´t have 2005 on my machine. First I thougt I could restore it to SQL2000, but I have understood that this is impossible. SO the wat to go is to restore the db to SQLExpress.
I have succesfully restored the db in Managment studio. But I have problem to connect my DNN3 site to it. I don´t understand how I should write the connectionstring in DNN3. In DNN4 is the folder App_Data where the database.mdf file is. But should I set it up the same way with DNN3? I have tried "all" but can´t figure out how to do it. Oneone having a solution for this?
// Regards Petri
By pehu on
1/30/2007 10:12 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
The example above uses Integrated Authentication (aka: Windows Authentication). If you pass the id and password in the connection string then the connection string would be like the other connection string in the web.config that is commented out. When using SQL authentication you can name the user anything you want.
By smehaffie on
1/30/2007 10:15 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Dear Shawn
Thank you so much this solved my problem big time.
i'am running dotnetnuke on iis 5.1 aspnet 2.0 and sql 2005 express
i made the mistake off using the default database.mdf and had as such no way off backing up my site was a long way gone yet and i didnt want to do that all again. All i tryed i could not get the connection string right your manual did the trick.
Thank a million.
Grtz Wim from the Netherlands
ps excuse my poor english
By wvdbroek on
2/19/2007 1:20 AM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Thanks for solving a killer problem... DNN may work well but the initial setup (sql connection strings) can be major, major time bombs. Get it right and your fine... get it wrong and you may die a slow death.
sundownr
By sundownr on
3/1/2007 11:49 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Great instructions, worked like a charm. Would love to know why.
Small suggested mods:
"- If you're fixing a problem with an existing DB problem, first detach the database in SQL Management Studio, then proceed with the 'Attach' instructions below. E.g. if you're putting DNN in an existing DB, or you've created one and you don't want to recreate it."
Also, in section "Adding Database User":
Check the ASPNet user and click "OK" button. Click the OK button on the Select Login screen and this will add the user.
>NEW> It actually adds the login, not the username. Now choose a username for the login.<<
Under database role membership check one of the following combinations.
...
>> NEW>> Recommended: do not choose a schema for the user (e.g. leave them all unchecked). [Is this the recommendation?] <<
By pkreipke on
3/1/2007 11:51 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Shaun - You are wonderful! Sebastian Leupold indirectly pointed me to this blog. I followed your instructions and voila! - my site came back.
I am disappointed that this tool could have caused such a problem and have suggested that a big skull and crossbones be put in the web.config along with a link to your article, because it seems so many DNN users have fallen into this trap.
Again - MY THANKS
By brads99 on
3/1/2007 11:49 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Thanks Mr. Shawn, your article was the key to resolve the problem with my dnn dev site, after hours intending repair, thanks again.
By mloptru on
3/15/2007 8:44 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Glad this article was so helpful. I do not blog alot, but when I do I try to address things that seem to be issues for multiple people.
By smehaffie on
3/15/2007 8:46 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Thnx for the tutorial!!:-)
But i have a strange problem...
It seems that everything went allright after install,(all items succeded) but my site doesn't working...
When im going at the Login , the explorer shows a dead link!!
I'm using Windows 2003 server x64 and SQL Server 2005 Express with IIS 6.0
My system runs a Domain Controler
Please help..!!
By Lambros on
3/18/2007 1:28 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
On XP 64-bit you had to set a registry setting to allow 32-bit applications. Not sure if the same is true with Win2003 64-bit. I'd do a search to see if there is any information about running 32-bit web applications on Win2003 64-bit. I know in Longhourn / IIS 7.0 it has a setting to tell it to run the site as a 32-bit application.
By smehaffie on
3/18/2007 1:31 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
OMG - I spent 6 hours scouring the net for some info for making SQL Express behave - all the default stuff let just one user on locally, and read-only acces via web (ie no DB-updates). The MS documentation totally sucks for non-DBA's like me - could have been written in Chinese (no offense to the participants from Zhong Guo)
I ran into your blog, and 30 minutes later I was up and running !
Maaaaaaaaaaaaaaaaany thanks !!!!!
By spikepl on
7/19/2007 1:59 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
great article, now if we could get someone to write somethign similar for web server 2000, iis5.5, and sql2005 db, separated on different servers, that'd be great!! any one know what the connection string and permissions need to be set for this?
thanks
By wsinclair on
7/19/2007 1:59 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
I have found another solution to this error that may be easier for folks to fix.
For some reason, SQL Server Management Studio removes the file permissions for the ASPNET account from the Database.mdf and the associated log file (Database_log.ldf).
To fix, all you need to do is give the ASPNET account "Read & Execute" and "Modify" permissions to the .mdf and .ldf files. (Right-click on the files, go to the Security tab, add the ASPNET user, and click the appropriate permissions checkboxes.)
You'll need to do this after every time you open the database from the Management Studio, so the blog post may offer a better long-term solution to folks who need to access the database often. However, if you're not comfortable with changing connection strings and setting up database users, this might be a more user-friendly approach to fixing the problem.
By mindviews on
7/19/2007 2:00 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
spikepl, Glad to hear this blog was beneficial to you, even for a non DNN database.
By smehaffie on
7/19/2007 2:01 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Great Article - Thank you!!
I spent 1 & 1/2 days trying to setup Dotnetnuke with Express and Server Management Studio on an EasyCGI VPS with no luck... then i stumbled across this pot of gold and 15 mins later everything was running. This info is vital and I think should be included in the install manual.
By wallacea on
8/11/2007 10:05 PM
|
It worked!!!
Thanks!!!
By mombassa on
9/26/2007 12:00 AM
|
Re: DNN / SQLExpress / SQL Server Management Studio
I cannot add login Network Service. When I find this username and press Add to add it to the logins in SQL Express, the error message apears: "Create failed for Login 'SRV001\NETWORK SERICE'.
Windows NT user or group 'SRV001\NETWORK SERICE' not found. Check the name again. (Microsoft SQL Server, Error: 15401)"
Should I use different user or create a separate user under SQL?
BR
/Mike
By mziemba on
10/23/2007 9:44 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Not sure if that is installed when .Net is or not, but it sounds like something is corrupt. Try to reinstall .Net, if that does not work I'd contact MS to see what the best way to add this user. This is an internal account, so manually adding one probably won't work since you would not know how to set all the default permissions correctly.
By smehaffie on
10/23/2007 9:48 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
I Have this exact problem. I attached the database in Studio manager now the site does not work. I followed the directions above, still I get This site is currently unavailable. Please check back later. If I give the database a more friendly name in management studio, How does the website reconize that name if I change it in the web.config? Would I not have to change the physical name of the MDF file? Thanks, I really need to get my new store front up.
By KillenKarting on
12/31/2007 1:35 AM
|
Re: DNN / SQLExpress / SQL Server Management Studio
KillenKarting,
No you do not need to rename the mdf file, since in step #3 you change the connectionString to connect to the database using the SQL Server name and not by attaching using the AttachDBFileName. Whatever you set the friendly name to in SQL Server Management Studio is the name you sent the Database equal to (in the post above I use DotNetNuke_ModDev as an example).
You might have to recycle the AppPool that the site is running under if running under IIS6 or IIS7. For IIS5, you might need to restart IIS. This clears any cached connections. Let me know if this helps you get your site up an running.
By smehaffie on
12/31/2007 1:42 AM
|
Re: DNN / SQLExpress / SQL Server Management Studio
--Just want to add that you can Creat Database with Attache use the following Command from SSMS
CREATE DATABASE New_Database_Name
ON (FILENAME = 'Full_Path\Database.mdf')
FOR ATTACH
----------------------------------------------------------------------------------------------
--Create New Login to your database:
USE [master]
GO
CREATE LOGIN [New_Database_Login] WITH PASSWORD=N'New_Database_Login_Password', DEFAULT_DATABASE=[New_Database_Name], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [New_Database_Name]
GO
CREATE USER [New_Database_Login] FOR LOGIN [New_Database_Login]
GO
USE [New_Database_Name]
GO
EXEC sp_addrolemember N'aspnet_Membership_BasicAccess', N'New_Database_Login'
GO
USE [New_Database_Name]
GO
EXEC sp_addrolemember N'aspnet_Membership_FullAccess', N'New_Database_Login'
GO
USE [New_Database_Name]
GO
EXEC sp_addrolemember N'aspnet_Membership_ReportingAccess', N'New_Database_Login'
GO
USE [New_Database_Name]
GO
EXEC sp_addrolemember N'aspnet_Profile_BasicAccess', N'New_Database_Login'
GO
USE [New_Database_Name]
GO
EXEC sp_addrolemember N'aspnet_Profile_FullAccess', N'New_Database_Login'
GO
USE [New_Database_Name]
GO
EXEC sp_addrolemember N'aspnet_Profile_ReportingAccess', N'New_Database_Login'
GO
USE [New_Database_Name]
GO
EXEC sp_addrolemember N'aspnet_Roles_BasicAccess', N'New_Database_Login'
GO
USE [New_Database_Name]
GO
EXEC sp_addrolemember N'aspnet_Roles_FullAccess', N'New_Database_Login'
GO
USE [New_Database_Name]
GO
EXEC sp_addrolemember N'aspnet_Roles_ReportingAccess', N'New_Database_Login'
GO
--------------------------------------------------------------------------------------
Update the and strings in the DNN web.config file with following for SQL Server Exspress Instance
Data Source=(local)\SQLEXPRESS;Database=New_Database_Name;uid=New_Database_Login;pwd=New_Database_Login_Password
By gr8scott00 on
11/21/2008 10:34 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
I just got the same problem today, thanks, you make may day.
Best Regards
Fernando
By fssv on
2/17/2009 3:36 AM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Fernando, glad the post is still helping people more than 2 years since it was written. I do not post as much as I probably should, but when I do blog I try to make it something that will be helpful to the community.
By smehaffie on
2/17/2009 3:38 AM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Wikipedia defines ELEGANCE: "The proof of a mathematical theorem is considered to have mathematical elegance if it is surprisingly simple yet effective and constructive; similarly, a computer program or algorithm is elegant if it uses a small amount of intuitive code to great effect."
I have struggled with my IIS, database, SQL SMSE, and web.config settings ad nauseum! The directions you provided in this post were simple elegance defined and saved DAYS of work for me. Thanks a MILLION!!!
By danabs on
8/2/2009 11:03 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Whoohaaa it worked. After struggling for days. Reading your post was a magical gift.
Very good and needful tutorial. They should include this with the installation instructions
Thanks
By robertbravery on
11/4/2009 11:20 PM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Hi, this is driving me nuts! I have created a localhost installation of DNN and built my site and now I want to upload it to a web host. I can attach the database to SQL Express and my site still works locally until I change the connection string in the web.config file. I have followed all the instructions above, but still no joy. I am desperate to get this working! I am also a total newbie, so any instructions will need to be step by step.
By michellb on
9/5/2010 10:47 AM
|
Re: DNN / SQLExpress / SQL Server Management Studio
Thanks so much.I had same problem and now I can connect sqlserver.
Thanks again!
By leotran on
9/5/2010 10:47 AM
|
|