I've created a visual C# GUI that logs data over a LAN. The GUI is for Admin purposes only so it logs data and stores it (plus some admin stuff). The data that is logged is supposed to be served up to anyone on the network as a web app. I've got an instance of SQLServer running and the GUI works great but I can't seem to get the web app to connect to the database to read and display the data for general users?
I'm new to ASP.NET and SQL Server so a little extra explanation or steps that I'm missing wouldn't hurt me if you have the time to spare.
I get an error that says "Cannot get web application service" in Visual Studio when I try and drag and drop a SQLDataSource onto my pages and configure it. It then asks me to Choose My DataConnection from the drop down and there's no connection there...
I tried setting it up in the code behind page like I would in Visual C# (small code example below) by adding in using System.Data.SqlClient; etc.. and it throws me a nasty notice (see below). Obviously it doesn't like this user but how do I fix this so I can server up my DB to work for both my GUI and ASP apps? I noticed when looking at my DB that the owner is TUDOR\Windows is there a way to have multiple owners to a DB if this is what's wrong?
String conn = @."Server=TUDOR\sqlexpress;" +
"Integrated Security=True;" +
"Database=ATSDB";
// Specify SQL Server-specific connection string
SqlConnection dbconn = new SqlConnection(conn);
// Create DataAdapter object
SqlDataAdapter dbadpt = new SqlDataAdapter("SELECT FirstName FROM tblPersonInfo WHERE FirstName = 'Kim'", dbconn);
// Create CommandBuilder object to build SQL commands
SqlCommandBuilder dbcmd = new SqlCommandBuilder(dbadpt);
// Create DataSet to contain related data tables, rows, and columns
DataSet dbset = new DataSet();
// Fill DataSet using query defined previously for DataAdapter
dbadpt.Fill(dbset, "tblPersonInfo");
Server Error in '/ATS' Application.
Cannot open database "ATSDB" requested by the login. The login failed.
Login failed for user 'TUDOR\ASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open database "ATSDB" requested by the login. The login failed.
Login failed for user 'TUDOR\ASPNET'.
Source Error:
Line 32: Line 33: // Fill DataSet using query defined previously for DataAdapter Line 34: dbadpt.Fill(dbset, "tblPersonInfo"); Line 35: } Line 36: } |
Source File: c:\Inetpub\wwwroot\ATS\text\Default2.aspx.cs
Line: 34 Hi there,
ASP.NET web applications, by default, will run under their own account. In your case, this account seems to be TUDOR\ASPNET.
The problem seems to be that this account is not set up as a login in SQL Server. You need to create a login for TUDOR\ASPNET for your SQL Server and give it access to the required databases.
You can do this by (sorry if you already know this bit):
1) Open Management Studio Express (I presume from reading your code that you are using SQL Server Express Edition) and connect to your SQL Server Instance
2) Expand the node for your SQL Server instance and you will see a node labelled "Security"
3) Right click on the "Security" node and select "New > Login" from the context menu that appears
4) A new dialog window will appear. Enter "TUDOR\ASPNET" as the user and make sure that the Windows Authentication radio button is selected. Set the default database as required (in your case this will probably be ATSDB)
5) In the dialog, on the left hand side there should be an item called "Server Roles". Click on this and then define whatever server roles (e.g. sysadmin) TUDOR\ASPNET will have....What roles the user'll have is entirely up to you
6) In the dialog, on the left hand side there should be an item called "User Mapping". Click on this and then select which databases TUDOR\ASPNET will have access to (in your case, most definately select ATSDB). Also, select what database role (e.g. dbowner) the account will have....Once again, this is up to you to determine
7) Nothing else is particularly interesting so just click OK to finish the process
After you have given access to TUDOR\ASPNET to your SQL Server, you should be able to use your ASP.NET application without getting that error.
Hope that helps a bit, but sorry if it doesn't
|||That sounds like exactly what I need to do. I am using Sql Server Express, from what I've read that is what comes with Visual Studio 2005 but I can't find Management Studio Express? Is that something that only comes with Express Edition software? When I was asked to do this I went out and bought some O'Reilly books but I also bought a couple Express Edition books so I could get up and going fast so if the code looks unelegant that might be why or maybe it was the /SQLExpress that gave it away. Thanks for all the notes I didn't know how to do all that and if I can find Management Studio Express that would definetly solve all my problems. Is it under something else in Visual Studio 2005 Pro Edition?|||Hi there,
Management Studio Express is a seperate download. You can get it from the below link.
SQL Server Express Edition Downloads
After you download & install it, you should be good to go. When Management Studio asks you to connect, your server name, by reading your code should be TUDOR\SQLEXPRESS.
Using Management Studio Express you can connect via SQL Server Authentication (using an account like "sa") or Windows Authentication. If you connect via Windows Authentication but find you can't modify users then your account probably does not have the required privileges. If you switch to the "sa" account you should be fine.
Hope that helps a bit.
|||Thanks so much for your help and your explanations. That's a lot of typing to do to help out someone you don't know. Much appreciated. Works great.
