Desparate: Problem connecting to MSDE database from Visual Studio
High All,
I'm getting really desparate as I've been going round in circles for weeks now trying to resolve a really annoying problem.
I'm having problems getting Visual Studio 2003 to connect to MSDE databases.
As per instructions on microsoft website - I've installed an instance of MSDE called VSDOTNET, started said instance, and then granted permission to the ASPNET user account to enable the connection to the server instance. I then installed the Pubs database to the same instance and granted access to the Pubs database to the ASPNET user account. I then tried creating a connection to the Pubs database via the Server Explorer in Visual Studio .Net but connection failed. I then used sp_addrolemember to make ASPNET db_owner but still the error persists.
The book I'm using as a study aid states that in the Data Link Properties window of the Server Explore window I should select SQL data provider and then, on the Connections tab of the window, select "Use Windows NT integrated security" before selecting the Pubs database from the drop down labelled "Select the database on the server". On attempting to select the database from the dropdown no database are shown and after several seconds an error occurs stating;
"[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied"
Stangely, when I expand the SQL Servers icon in VS .Net Server Explorer I can actually browse and view the data in the tables of the Pubs database which makes this error even more confusing for me.
If anyone can help your advice would be much appreciated.
Thanks
Mike
[1689 byte] By [
mjoc69] at [2007-11-11 6:59:24]
# 1 Re: Desparate: Problem connecting to MSDE database from Visual Studio
Essentially you appear to be connecting two different ways. One is via Server Explorer in the Visual Studio IDE and the other ASP.NET.
When you use Server Explorer and integrated security, the credentials used for authentication with SQL Server (or MSDE) will be those of the local user account - those used when logging on to Windows (or the Network).
When you connect via an ASP.NET application, the credentials used under Integrated NT authentication will be those of the ASPNET account (or NetworkService account under 2003 Server) by default.
# 2 Re: Desparate: Problem connecting to MSDE database from Visual Studio
Thanks for replying, your help is very much appreciated.
Could you please confirm if my understanding is correct as this has all been quite a steep learning curve for me so far.
In order to create an asp.net application in Visual Studio I must use my own user account, but in order to establish data connections to MSDE I need to grant my user account permission to use MSDE instance and any databases it contains. Once the asp.net application is compiled the .net framework will then take over and access MSDE instance using ASPNET user account. Just a point, i noticed the account created by the VS install is named ("ASP.Net Machine A..." literally) - is this correct or should it be renamed ASPNET. Forgive me if this is a stupid question.
Thanks again
Mike
mjoc69 at 2007-11-11 21:50:45 >
# 3 Re: Desparate: Problem connecting to MSDE database from Visual Studio
In order to create an asp.net application in Visual Studio I must use my own user account, but in order to establish data connections to MSDE I need to grant my user account permission to use MSDE instance and any databases it contains.
I'm not sure if I understand the first part of your question, but yes, if you are specifying Windows Integrated authentication in your connection string then your account will need sufficient permissions to the MSDE database objects you intend to access through the VS.NET IDE (e.g. Server Explorer).
Once the asp.net application is compiled the .net framework will then take over and access MSDE instance using ASPNET user account. Just a point, i noticed the account created by the VS install is named ("ASP.Net Machine A..." literally) - is this correct or should it be renamed ASPNET. Forgive me if this is a stupid question.
Whenever you run your ASP.NET application, whether from the IDE or outside of the IDE, the ASPNET account is used (NetworkService in Windows 2003 Server) by default. You can implement impersonation if you want to use the authenticated credentials (instead of ASPNET):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconaspnetimpersonation.asp
The account you mention sounds like the description and not the actual account ID. ASPNET should be correct (unless you've manually changed it).
# 4 Re: Desparate: Problem connecting to MSDE database from Visual Studio
Hi Paul, thanks again for your advice.
Still having problems unfortunately. My understanding of the workings of the security is improving but alas I am not making any progress with my studies.
I'm attempting to study ASP.NET using the microsoft press book "MCAD/MCSD Self Paced Training Kit - Developing Web Applications with VB.NET and C#.NET. I was basically flying through the first few chapters until I got to the Data Access chapter and that is where the wheels fell off.
The way I understand the security, is that the administative account I used to install both VS IDE and MSDE instance should enable me to add a connetion via the VS IDE Server Exporer to the Pubs Database within the VSDOTNET instance. The steps to achieve the connection are well described in the book I mentioned earlier. The ASPNET account is only used when the asp.net application is running and is managed by the .net framework.
As mentioned previously, when I attempt to use Windows NT integrated security as per the instructions in the book I receive an error when trying to select the Pubs database from the drop down labelled "Select the database on the server". The error states;
"[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied"
What I omitted to mention in my original post is that a further message box appears subsequently stating:
"Login failed. Catalog information cannot be retrieved."
I'm at a complete loss. The setup seems relatively straightforward yet I can't seem get beyond this point. I lost count of the number of times I've reinstalled all the apps as well as the OS several times and I've even removed antivirus and switched off the firewall before reinstalling everything.
I installed MSDE as per microsoft article using the only Admin account on my XP Pro box http://support.microsoft.com/default.aspx?scid=kb;en-us;872905
One thing I have noticed is that the SQL Service Manager on the System Tray has a status of "Not connected" even though when I check the Services via services.msc the VSDOTNET instance is shown as "started". I did remember reading a Microsoft article some weeks ago that suggested that further services should also be started but I remember that i could not find 2 of these in the list of available services. One related to LDAP, the other I can't rember. I assume they relate to other versions of Windows and not XP Pro.
Sorry for sounding so hopeless but ....
Thanks again
Mike
mjoc69 at 2007-11-11 21:52:46 >
# 5 Re: Desparate: Problem connecting to MSDE database from Visual Studio
I don't think reinstalling is going to fix your problem since configuration is likely the issue. The account you used to install the product has no bearing on the which identity is used to access MSDE.
The article you posted describes how to grant permissions for the ASPNET account. Did you grant permissions for the Administrator account (or account you're using to log on to Windows with) as well? This is the account that will be used when connecting to MSDE under Integrated Windows authentication from the VS.NET (not ASP.NET) IDE.
You may also want to try the osql utility to see if you can establish basic connectivity.
http://support.microsoft.com/?id=325003
Below is an article that may also help:
http://support.microsoft.com/?id=814130
