Would someone please take a minute to give me a step by step how to add an .mdf db to a web app(VS 2k5 Pro with SSE)?
Only one app in the project intended as a complete sample solution to be zipped and distributed to people who would have a similar VS setup. The idea is the recipients to be able to open the .mdf (or the pages using it) right out of the box without having to go to hell and back. The .mdf included in the App_Data and the connection specified in the web.config. All ready to just unzip and go.
Obviously, it would have to be sql authentication.
Say user: johndoe/ pw: 123456
Amazing how the db connection process still has a way of bringing otherwise intelligent people to their knees.
TIA
Step 1 - New web Site :-)
Step 2 - Open Server Explorer (Ctrl W,L)
Step 3 - Right Click Data Connections and Choose Add Connection
Step 4 - Change the Data Source to be a Microsoft Sql Server Database File using the Change button
Step 5 - Use the Browse button to create a new MDF file in your App_Data folder.
Step 6 - Now you should get a dialog asking if you want to create the file. Press Yes and you should now have a Data Connection in server explorer which you can use.
Hope that helps
|||
Forgot to say that between step 5 and 6 you should select SQL authentication instead of windows authentication.
|||
Hi James,
Thanks for taking the time. I also thought that is what it takes before I started playing with SSE.
The steps you listed only work for windows authentication mode. If I select SQL authentication I get an arror:
Login failed for user '<mylogin>'. The user is not associated with a trusted SQL Server connection.
I tried all kinds of<mylogin>values Administrator, the current login, totally nonexistent names... They all give the same error. The idea is to put in a username/pw that have nothing to do with my machine. Isn't that the point of file based db (unless we are expected to deploy on the dev machine)? Anyway, it rejects all of them indiscriminately.
Back in sql 2k, this error was associated with not setting up globally the authentication mode of the server instance to either mixed or just sql.
With SSE there is no concept of multiple instances or global server settings for authentication, like with the full versions. I had installed VS a couple of months ago and there is no place in the options that shows the current configuration. So I uninstalled SSE just to go through the install all over again. Well, it just rolled along automatically. I didn't see anywhere during the install to set up the server. I assumed that was being done at the file level, as the GUI suggests.
Something just doesn't add up.
|||
Rob
All becomes clear. The default setup for VS2005 Express is to only support windows authentication. The setup doesn't allow you to change this during the graphic stage, you have to start setup with the correct command switch. The good news is that you can change it to mixed mode with a bit of registry hacking.
See thisknowledge base. Hopefully that should get you on the way.
Cheers
|||
Thanks James.
This only partly solves the problem. The recipients of the sample project will also face an issue connecting.
Am I misunderstanding the puspose of .mdf files? They do not seem to be transportable databases from one machine to another like Access. I can't expect everyone I send a sample project to hack their registry, just to try the sample. Most installs out there are exactly like mine - done from the setup media, thus default windows authentication mode. Why did they even bother showing the other modes in the GUI?
If I have to hack the registry to get a basic functionality I am by definition misusing the ptoduct. What is the right way to accomplish this, what I thought was a simple and popular, task? Namely to send a one page example along with a db all packaged together?
Using Access instead is not the same. Many features are different.Embedding SSE is a huge job too, and not really pertinent to web apps anyway.
|||
I only mentioned the registry hack for your machine.
If you install SQL Server Express with the switch SECURITYMODE=SQL then it will be in Mixed mode authentication.
Then you can ship the mdf file. This article has a good story about how to embed Server 2005 with your app -http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/EmSQLExCustApp.asp
Cheers
James
|||Yes, I am aware of this article about embedding. As I said it is not straight forward and it does not pertain to the simple scenario I describe.
OK. Clean new xp sp2 OS install.
Clean new NAMED instance SSE install with mixed authentication mode specified at setup.
I still get the exact same error, if I try to create a new .mdf with sql authenitcation.
What gives?
|||1) New website.
2) Right click "App_data" in solution explorer.
3) Click "Add new item"
4) Click "SQL Database".
5) Click "Ok".
Is there something wrong with this particular setup that isn't working for you?
If you then drag a SqlDataSource to your webpage, and select the connection property, one of the entries in the dropdown should be "database.mdf", select that. If you then go to the .aspx source, you should see that the connection string it is using is:
ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"
There is no mention of the machine name, or the project directory in there, and you can just .zip the project folder, give it to someone else, and they can use it immediately.
|||ok. So an .mdf with just windows authentication works on any machine it is on.
I still have to digest the sucurity implications, but for now insecure is exactly what I need so ticket closed!
Thanks to everybody.
|||I recently re-installed visual studio 2005. Since that time I have not been able to connect to .mdf files or create them in the App_Data directory. I receive the "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." error. Anybody have any ideas as to what is causing this?
Thanks
No comments:
Post a Comment