Hello,
I just downloaded and installed the Visual Web Developer 2005 Express which is part of the new issued Visual Studio 2005 Express. It includes SQL Server 2005 Express, so I also installed it.
My problem is I can't connect to the database supported by SQL Server Express. The log in is failed.
I hope anyone who is using SQL Server Express can give me a help and tell me what wrong I've done.
What I've done are:
1. In the Database Explorer, right-click the Data Connections, I create a new database named "lavender", so it's "lavender.mdf".
2. Then I go to Tools->"connection to database...",
(1) then in the pop-up window, I chose the "Data Source" as "MS SQL Server Database File(SqlClient)". I ever tried to select "MS SQL Server", but it couldn't pass through the following step(4), not sure if SQL Server Express can only be chosen under "MS SQL Server Database File"?;
(2) Then database file name, which is to attach to the SQL Express Server, is "C:\Inetpub\wwwroot\lavender\App_Data\lavender.mdf";
(3) Then "log on to Server", I use "Windows authentication";
(4) Then I "test the connection", it works.
3. Then I create some table in the lavender.mdf database under the Database Explorer.
But when I run the connection.Open() code and some other sql codes inside a vb file in this lavender project, it can "successfully build" Website without error, but can't show me results in IE6, only shows me the following error:
Server Error in '/lavender' Application.
Cannot open database "lavender" requested by the login. The login failed.
Login failed for user 'HENRYPC\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 "lavender" requested by the login. The login failed.
Login failed for user 'HENRYPC\ASPNET'.
Source Error:
Line 8: Dim command As New SqlCommand("GetDepartments", connection) Line 9: command.CommandType = Data.CommandType.StoredProcedure Line 10: connection.Open() Line 11: Return command.ExecuteReader(System.Data.CommandBehavior.CloseConnection) Line 12: End Function |
Source File: c:\inetpub\wwwroot\lavender\App_Code\Catalog.vb
Line: 10
Stack Trace:
[SqlException (0x80131904): Cannot open database "lavender" requested by the login. The login failed. Login failed for user 'HENRYPC\ASPNET'.] |
I tried many different ways to connect to my "lavender" database before I seek help here. Really feel tired and don't know which point I've configured wrong.
I have windows IIS, and the Website can show up if there is no these sql codes or no need to connect to database. I am still not clear if my lavender database is already located in the SQL Server Express. If already there, how can I connect to it? Why log in fail while I am the only user of my computer and using Windows Authentication?
Thank you very much for your help! Have a good week!
Henry LiHenry,
I don't pretend to be a sql guru, but something that looks odd, is that in your error, it is indicating attempting to use windows authentication with the ASPNET user account. Even though you are the one logged onto your box, IIS uses the ASPNET to run .Net applications, not your userid. You will need to give the ASPNET account access to your database if you want to use windows authentication. I would imagine that you have set your database up to just allow your userid access.
Incidentally the reason, it works in Visual Studio is that Visual Studio authenticates using the credentials of the person logged into the box. Hence it is using your user account.
Either add ASPNET to your database or create a new login and password in SQL Server and don't use windows authentication.|||Hello Jon,
Really thanks for your quick help! I reckon you've already found out where I'd done wrong.
I just wish you could give me a detailed instructions how to add the ASPNET into my database account. And sorry as I am a beginner just setting up the SQL Server (Express) and VS Express, also a beginner handling with database or server.
And when you said "ASPNET account access to my database", do you mean my whole database or just my lavender.mdf data file(it's a single file holding a lot of forms & stored procedures inside it)?
If you mean my whole database, do you mean my SQL Server Express, are these two the same thing? I am also curious whether my lavender.mdf is already in my SQL Server database.
What I want to know most is how and where to set up the ASPNET account in my database (or in my SQL Server Express?). Could you please kindly tell me in details? Thank you in advance. I am not going to set it as SQL authentication with User ID & password at this moment, just because I am not so sure if I can do it well.
By the way, how to set the paramenters in the web.confi file? Is the code below correct?
<appSettings>
<add key="ConnectionString" value="Server=.\SQLEXPRESS;Integrated Security=True;Database=lavender.mdf" />
</appSettings>
Thank you very much indeed!
Henry Li|||Henry,
At the present time, I'm having issues with my SQL Server install as well, so I'm afraid I can't give you a clear walk through of adding the necessary security permissions to your server.
However, for the moment, I can answer some of your questions.
First off, when I referred to database, I was referring to your lavendar.mdf database. SQL Server Express is an engine that can contain multiple databases. Each *.mdf file is considered an individual database that SQL Server Express understands.
Second, while it is possible to add security to all your databases, if you're going to use windows authentication, it would be best to add just the individual account to the individual database. That way you don't open up holes to all your other databases you might have or create in the future.
Thirdly, I'm very familiar with how to add security to a database in SQL Server 2000, but I'd hesitate to tell you that is how it is done in SQL Server Express. As soon as I can get mine installed and working correctly, I'll let you know detailed steps.
On a side note, your connection string looks fine to me and the error you were getting also indicates that your web application can find the database just fine. It just can't authenticate to him.|||Hello Jon,
It's very nice of you. Thank you indeed!
Your reply gives me a better view of my problem.
Let me share how I install SQL Server Express.
I installed the SQL Server Express as a component of Visual Web Developer 2005 Express (440MB), free downloaded from microsoft.com. I don't know what problem you met with your installation, and as I am not a professional, I am happy to tell what I've done but not sure if it will help.
Visual Web Developer 2005 Express is part of recent release of the Visual Studio 2005 Express, it's a single file WEB.img(440MB), I downloaded it and then used the Nero Burn Room CD burning software to burn it into a CD, then it became scores of files. Then it started automatically the installation, which includes 4 components, the Windows installer 3.1, the VWebDep Express, MSDN Express, and SQL Server 2005 Express.
As I just upgraded my computer from XP Home to XP professional 4 days ago, and just set it up as a local server with the help of IIS 3 days ago, I don't know how to configure the database and the connection. I also got the MSQL Desktop Engine(MSDE) installed in my computer 3 days ago before I installed the SQL Express, they get on well with each other very well.
--
I wish you could give me a hint how you configure your SQL Server 2000, so that I can have some idea how to deal with the Express.
What I've done with SQL Express is: (but can't go throught):
I opened the "SQL Server Configuration Manager" and tried to find where I can configure database log on. Under the "SQL 2005 Server Service" -> "SQL Server(SQLEXPRESS)", right-click it for properties, it gives me 3 tabs.
One tab is "Log on as", there are 2 choices, one is "built-in account" which include 3 choices, local system, local service, and nextwork service.
The other choice is "this account" with account name and password and confirm password for me to enter. I don't know what it means. Is it for the system admin to log in or for creation of endusers' log-in? It has a "browse" button beside account name to browse some names.
But when I enter ASPNET as an account name, and give it a new password, it refresh the Server Express then pop me a window "The specified network password is not correct". Then I don't know how to deal with it.
The other two tabs are "service" about binary path and starting mode etc, and "advanced" about clustered, startup parameters, registry root, etc. No idea about it.
I hope you will work on well with you installation, and hope you could give me a hint about your log-in configuration of Server 2000 as your explanation I find is always very clear!!
Thank you in advance, have a nice day!
Henry Li|||Hello Jon,
I just installed the MS SQL Server Management Studio Express (CTP), and I found there are plenty of options inside to set up security and add new databases!
Thanks a lot!
I added a HENRYPC\ASPNET user besides other Buildin\users and sa, etc. Then I found under the DATABASES, I just got system databases, then I created a "EXPRESSDATA" for my own use.
Then I modify the connection in the Visual Web Developer Express under the Database Explorer. It connects successfully to the EXPRESSDATA under the .\SQLEXPRESS data source!
And I still got one error when I run the project in the IE6 browser: can't find the stored procedures "GetDepartments". So it seems it connects! but maybe there is still some issue related to these tedious connection, because I have the stored procedure there under the "lavender.mdf" database, but can't see it in the Management Studio Express.
I am still trying to solve it. Thanks a lot for your help! Now I have a clearer understanding of these complicated connections.
Best wishes,
henry Li|||Henry,
I'm glad you've figured your connection stuff out. I'm glad to know that what you were looking for was in the Management Studio. I kind of thought it might be, but I still haven't been able to get my install working to verify it.
The reason it can't find your stored procedure is that you hooked up to a new database called ExpressData instead of your lavender database. Your stored procedure resides in lavender instead of ExpressData.
The best thing to do would be to try to get Management Studio express to see your lavender database. You should be able to do this by right clicking on the databases tab or folder and there should be an option to attach or import an existing database. Again, I don't have it installed, but that is how it functions in SQL Server 2000.
If you can get the lavender database seen in Management studio, then you'll want to change your configuration file to look at that database again instead of ExpressData. In addition, you'll want to add the aspnet account to have access to the lavender database.
Again, once I can get my install working I'll be able to provide you with specific steps. Good luck!
