Preface, I am just starting to learn about database and web development.
I installed VS.net 2005 which updated my web site project file and automatically attached it to a database. It connected to ".\app_data\aspnetdb.mdf". However it messed something up and the connection didn't work. If I create a project from scratch, it connects to its DB just fine, but not my upgraded project.
However, when I publish my web site, I need to use ODBC. So, my thought was to simply set up the system locally in a way that will make it easy to connect on the web site. I was told to add a system DSN to the "Microsoft ODBC Administrator". I tried to add a "SQL Server" and give it the database name but it failed to connect.
So, then I ran the SQL Server Configuration Manager and looked at the server properties and found this string under the advanced -> startup parameters.."-dc:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-ec:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lc:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf"
My database is in an entirely different folder. The database it looks like it is servicing is a "master.mdf" database installed under the SQL server install folder. So, I figured that I found my problem. I just have to change this file to map to mine. I replaced these three strings with paths to my aspnetdb.mdf/ldf files, and created a LOG folder. Howwever, it wasn't able to connect to this database. It said something like "it doesn't exist or you don't have permission", even though I gave it the right path.
So, I'm at a loss for how to get my application to be able to use the ODBC interface to connect to its very own database on my local system.
What do I need to do? Please help!
You need to create a Windows Authentication account on the server for ASPNET with a password.
When an .aspx page loads and tries to access the server, it uses Windows Authentication unless you code the connectionstring to use SQL authentication.
After you create the ASPNET Account, make your connection string look like this:
connectionString="Data Source=ODC01;Initial Catalog=OEM;Integrated Security=True"
Adamus
|||Thanks you for the feedback. Unfortunately I'm still a bit confused. My problem is that I don't have a complete picture of how this is supposed to work.
Are you saying that I must set up a the server to use an account that uses Windows Authentication before I can attach it to my own MDB file? In other words, it will only connect to the default master.mdb file until I create an account?
Also, using "local" or "system" isn't good enough, I have to specify a user account even on my own personal computer running XP? I am the only one who uses this system, and there is no windows server running on the network. This is my home setup.
This is the map that I understand. There are 3 connections that must be made for this to work.
mydb.mdb --1 (get server to serve my DB)--> SQL Server express 2005 --2 (tell ODBC about my DB exposed by the server)--> ODBC --3 (using the connection string)--> Website.
Right now I beleive none of the 3 connections are working, and I feel like I need to get the step 1 done before I can establish and verify step 2 and then finally use the connection string in step 3.
Are you saying that I must create a Windows Authentication user account before step 1 will work? Does that simply meen that I create a user called "SQL Server" with minimal access on the computer and log into it whenever I want it to connect?
Is there a "complete idiots guide to establishing a SQL server 2005 connection from your web site to your database" book that I could buy?
|||I take it all back. You were right. Now I understand. I actually had the account set up correctly on the server side, but I wasn't logging in correctly.
Second, when I tried to attach the server to ODBC I was using my computer name alone, when I needed to use <computer_name>\SQLExpress.
Now I have to figure out the connection string. But I am finally over my biggest hurdle. Thanks!
Firstly you should ensure you have enabled remote connection of the SQL Express from 'SQL Server Surface Area Configuration'; then enable TCP/IP and Named Pipe protocol on both Server and Client side in 'SQL Server Configuraiton Manager'. After that, if you still have problem, you should check your conneciton string.|||thank you very much, now i am all set to go