Visual Studio 2005, SQL Server 2005 Database, aspnetdb - Unable to Connect

I have just created the ASPNETDB database with the aspnet_regsql.exe utility in the Windows\Microsoft.net\Framework\v2.0.50727. I can see the database in SQL Server Management Studio. The database itself has a user XXXXXX\ASPNET. It has "CONNECT SQL" and "VIEW ANY DATABASE" Effective permissions. When I add permissions to the ASPNETDB, I am have 13 ASPNET Database Role objects that can be added to the permission list. Can anyone give me guidance as to what permissions need to be granted to what Database Role / User?

Presently the Web Site Administration Tool is unable to connect to the SQL Server database.


Nope
0
hwalhout
8/16/2007 10:55:26 PM
asp.net.security 27051 articles. 1 followers. Follow

19 Replies
1028 Views

Similar Articles

[PageSpeed] 0

What's the error u have got when you try to connect to DB?

0
jz18
8/17/2007 2:31:13 AM

When I add the "Login" control object from the toolbar in Visual Studio 2005 in an ASP.NET Web Application, and then select "Administer Website", and selct the security tab, I get "Unable to connect to SQL Server Database" When I press the "Choose Data Store" Button, it tells me that my application is currently using AspNetSqlProvider. When I test the "Select a single provider for all site management data" I get "Could not establish a connection to the database"

I have added the ASPNET user to the database "aspnetdb", it automatically is granted "connect" permission. I have experimented with giving this user select and update permissions on the database. I am confused about the aspnet_Membership_BasicAccess and the other 12 database roles that can be added to the permission table. There does not seem to be any good documentation on the web or in MSDN that I can find. I have printed "Security Practices: ASP.NET 2.0 Security Practices at a Glance" but cannot find anything that is helpful. I presume my setup with a full SQL Server 2005 Database package and Visual Studio 2005 is common enough, and I am wondering if other users are experiencing the same problems that I have encountered.


Nope
0
hwalhout
8/17/2007 5:55:52 PM

I got exactly the same problem with an identical configuration (running WinXP Pro SP2, SQL Server 2005 and VS 2005). One thing I can add is that the Web Site Administration Tool was taking almost a minute before it would display the "Unable to connect" error message. I saw on a message posted on the aspnet newsgroup that suggested replacing the DataSource item in the Server Name tag in the machine.config file from "./SQLEXPRESS" to my server name. When I tried the tool again, it cam back with the error message immmediately. This suggests that there were at least two problems: a server connection problem and another, probably, a provider problem. The change I made seems to have corrected the first one. As for the second one, I can't find any reference in the machine.config file to the provider name "ASPNetSQLProvider". I hesitate to change anything in the Role or Membership sections since I don't fully understand the implications of such changes. I, like you, would welcome any help or guidance in resolving this issue.

0
zz_scarab
8/17/2007 6:26:53 PM

Thanks for the comments zz_scarab. I had a look at my SQL Server setup, and stopped all the SQLEXPRESS services. I now get the same problem you described, with the Web Site Administration Tool taking a minute to display. I suspect the Web Site Administrator is looking for the "aspnetdb" database in SQLEXPRESS. I will do some more experimentation.


Nope
0
hwalhout
8/17/2007 6:51:09 PM

I experimented a little more and changed the AttachDBFileName to point to the default location where the aspnet_regsql tool built my ASPNETDB.MDF (in the ...\MSSQL.2\MSSQL\Data forder), and I now get a different error message that it cannot open the database because it is in use by another process even though nothing else is running. I even shut down SQL Server Management Studio and got the same error message.

0
zz_scarab
8/17/2007 7:55:19 PM

I have just started using this also, but I might be able to help. After you've created your tables on the external database (SQL Server 2005), you can alter the provider in the machine.config, or create a custom provider. Creating a custom provider seemed easier to me. You place the custom provider in you web.config file, then go to your asp.net configuration and point your provider to the custom one you just built. Here's a sample of a web.config
<?xml version="1.0"?>
<configuration>
<
appSettings/>
<
connectionStrings>
<
add name="MyConnectionString" connectionString="Data Source=MySqlServer;Initial Catalog=Membership;Persist Security Info=True;User ID=MySQLUser;Password=####" providerName="System.Data.SqlClient" />
</
connectionStrings>
<
system.web>
<
authentication mode="Forms" />
<
roleManager enabled="true" defaultProvider="CustomRoleProvider">
<
providers>
<
add connectionStringName="MyConnectionString" applicationName="MyWebApp" name="CustomRoleProvider" type="System.Web.Security.SqlRoleProvider" />
</
providers>
</
roleManager>
<
membership defaultProvider="CustomMembershipProvider">
<
providers>
<
add connectionStringName="MyConnectionString" applicationName="MyWebApp" name="CustomMembershipProvider" type="System.Web.Security.SqlMembershipProvider" />
</
providers>
</
membership>
<compilation debug="false" />
</system.web>
</
configuration>
 

This works just fine as long as the user in the connection string is a member of the aspnet_***FullAccess roles in SQL.

 The part that confuses me is, do you have to set up multiple custom providers to have a user that's only a member of the aspnet_***BasicAccess roles?

0
bob808
8/17/2007 9:56:18 PM

Thanks, Bob. I tried that and got a little further but still not all the way. I can connect to the server and the database, which is now built in the web site's App_Data folder. My user is ASPNET and when substituting this into the connection string I get an error message that access to the Membership table is denied for this user. The user is a member of the aspnet_*** FullAccess roles, and in addition, to the db_ddladmin and db_securityadmin roles as well. Also, I'm able to connect when logging in as this user into the SQL Server Management Studio, and can open and modify all the aspnetdb tables. So I'm not sure at all why it's failing.

 Any more light you can shed on this?

0
zz_scarab
8/18/2007 1:55:38 AM

If your database is in your App_Data folder then your using Sql Server Express...right? The sample I gave you above is actually hitting an external database on another server. I haven't given the user named in the connection string any explicit rights on the db, instead I made them a member of the aspnet_*FullAccess Roles. There is a FullAccess Role for each kind of provider.

 If you want to use the database in your AppData folder, and it is was the one autogenerated by VS2005, then you don't need a custom provider at all.

 Does that help at all?

0
bob808
8/18/2007 4:51:33 PM

No, I'm actually using SQL Server 2005 not Express. The first time around I let ASPNET_REGSQL tool build the ASPNETDB database itself and it built it into my default SQL Server data folder. There I got the "Unable to connect" message. Then I deleted the database and built it manually using SQL Server Management Studio (SSMS) in the App_Data folder and reran the tool choosing "Existing Database" this time. The default location in the machine.config file that points to |Data Directory| let me then get past this point and complained about not having the correct permissions. I then did what you suggested and made the ASPNET user a member of all the roles you suggested and verified that this "took", but still got the message that opening the Membership table was denied to the ASPNET user (I changed the User ID and Password you supplied to the ASPNET user and its password). Like I mentioned above, I'm able to log into SSMS and manually open and modify all the tables in the ASPMETDB database so it doesn't sound from this angle that it should be a user id access issue, and I know the Web Administration tool is able to connect to the database because the error message indicates that it's getting past this point and attempting to open the Membership table using this ASPNET user id. So I'm quite confused.

 Of course, when I tried the original, all default stuff, I had no provider at all and that didn't work either.

Also, if I ignore the App_Data folder and let the tool build the database, it sounds like I should choose a different name than ASPNETDB so I can later on manage multiple sites targeted at different sets of users, and change the reference to the database in the machine.config file accordingly, right?

0
zz_scarab
8/20/2007 6:12:44 PM

Personnaly, I would leave the machine.config file alone. If you can, set it back to it's default.

Instead of manually creating the database, did you try using the aspnet_regsql.exe file. It will open a Wizard that will create everything you need on the server you want. If you absolutely have to manually build it, be sure you have also created all the stored procedures, roles, and schemas, and not just the tables.

I created my db with the aspnet_regsql.exe tool, and if you look carefully at the role permissions, you'll see that by design, no one actually has permission on any tables. Permissions are given to the roles on the stored procs (Execute). Each role is assigned different stored procs. Then the higher level role (full access) is a member of the basic access role therefore giving full access permission to all stored procs under that provider.

 Is this helping at all?

0
bob808
8/20/2007 6:30:58 PM

Thanks, Bob. I'll try starting over, restore the machine.comfig, use the ASPNET_SQLREG using its defaults and see what happens. It's possible that in all my changes I accidentally made one that I shouldn't have. I'll post an update later with the rsult.

0
zz_scarab
8/20/2007 8:03:09 PM

Can you shed some light on this default connection string in the machine.config file. It looks to me that the data source has to be SQLEXPRESS. What does the .\ mean. If it is a directory reference, I can't find it with the windows "Search" utility. Does it mean that you can change the datasource to SQLSERVER in this default string? I presume DataDirectory refers to App_Data. Where is that defined?

</providers>
  </configProtectedData>
  <runtime />
  <connectionStrings>
    <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />
  </connectionStrings>


Nope
0
hwalhout
8/20/2007 10:55:54 PM

Earlier when I was messing around with the machine.config file, I changed the Data Source to <MyServerName> , no "\" involved. I alon so found that I had to change the User Instance to false, and add my User ID and Password to the connection string. Note that the |Data Directory| refers to the App_Data folder, which according to Bob808 is normally used with the SQL Express edition, which neither of us is using. I changed my |Data Directory| reference to "C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data and this worked fine in the sense that it got me to the correct database. However, I still have the same problem that my ASPNET user is being deined access to the Membership table vern though the user id is a member of all the aspsnet_XXXFullAccess roles, and these roles all have permissions to the ASPNETDB database. Also, if I log into the SQL Server Management Studio using the ASPNET user, which is failing when accessed from the Web Administration tool, I can access all the tables in the ASPNETDB database to read, insert, edit, and delete. Hence my continued confusion.

0
zz_scarab
8/20/2007 11:30:20 PM

I wonder if it would be useful to get WROX - ASP.NET 2.0 Security, Membership, and Role Management for guidance on configuring the machine.config and running security databases outside of the App_Data folder. http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764596985.html I also found an article on the net on creating ASP.NET 2.0 Provider Models http://www.theserverside.net/tt/articles/showarticle.tss?id=CreatingProfileProvider You can have multiple providers specified (more appropriate in machine.config) and have different sites using different providers if necessary. I have tried to experiment with this approach, but have been unsuccessful so far. Any help on this would be greatly appreciated, since I have to use SQL Server 2005 which is located on a different machine from the webserver.


Nope
0
hwalhout
8/21/2007 3:50:55 PM

Thanks. I'll check them both out, and there may be quite a few others. A couple of sites that I found yesterday are www.4guysfromrolla.com, http://weblogs.asp.net/scottgu/  and www.odetocode.com. There's quite a bit to digest!

 Sounds like we're both in the same boat. I'll keep experimenting and post any findings, especially if successful. Hope you can do the same and maybe we'll eventually get there.

0
zz_scarab
8/21/2007 4:34:45 PM

Here is my version of the solution.

Prerequisites: SQL Server 2005 is configured for Windows Authentication.

1. Create an SQL database using aspnet_regsql tool;

2. Put the following connection string in the web.config file:

    <connectionStrings>
        <remove name="LocalSqlServer"/>
        <add name="LocalSqlServer" providerName="System.Data.SqlClient" connectionString="server=.;database=aspnetdb;Integrated Security=SSPI"></add>

    </connectionStrings>

3. Test the connection using ASP .Net Web Application Administration tool. The connection is established successfully.

It worked for me perfectly. Hopefully, it works for you as well. Good luck! Big Smile 

0
Poe
8/23/2007 6:47:40 AM

Worked like a charm, Poe. Thanks a million. Will there be any problems with deployment though using this configuration since Windows Authentication is not applicable?

0
zz_scarab
8/23/2007 6:01:48 PM

Hi again! I am really glad it worked for you Big Smile!

Regarding the question, I have not yet deployed a web-site, I have just started to learn ASP. However, I did some investigation, and that's what I found: if you are developing  a web-site that is accessed by external users, you need to use login and password coded explicitly in the connectionstring. Such string would look as follows:

        <remove name="LocalSqlServer"/>
        <add name="LocalSqlServer" providerName="System.Data.SqlClient" connectionString="server=.;database=aspnetdb;User ID=Hobbit;Password=12345"></add>

I have also found information on how to encrypt this connection string. Here are some useful articles:

http://msdn2.microsoft.com/en-us/library/ms178375.aspx#SecuringDataAccess_ConnectionStrings

http://msdn2.microsoft.com/en-us/library/dx0f3cf2.aspx

0
Poe
8/31/2007 10:23:37 AM

 HI,

I have sql server 2005 full edition so even i faced the same problem "Unable to connect to the Database server". By default these features are intended for sql express edition so we need to change the connection string in the machine config file which is located at C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG.

default connection string in the machine  config file is :

<add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />

just replace it with:

<add name="LocalSqlServer" connectionString="Integrated Security=SSPI;Data Source=machinename;Initial Catalog=aspnetdb;" providerName="System.Data.SqlClient" /> 

this solved my problem. hope it solves your problem too!!!!!!!!. 

 

 

 

0
mrudula
9/12/2007 3:41:02 AM
Reply:

Similar Artilces:

SQL Server 2005 express connectivity with Visual Studio.NET 2005
Hello I'm having a problem with the server connection and my C# code. The code executes alright but no data is entered into the database behind it.  I'm pasting the connection code over here as well. The sql commands are used in the code. I think I used the sql server mobile edition which is installed along with visual studio.net 2005. Anyone know what's wrong, and can help me out? Thanks in advance.public static void storeInDb(string trackName, string artist, string albumTitle, string year, string path, uint[] fp) { string connStr="Data Source=.\\SQLEXPRESS;At...

i am getting problem when i am connecting to database sql server 2005 from visual studio 2005
 the error is "An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"mine is a windows authentication server.  Hi v_badhra,see this article:http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277 Samu ZhangMicrosoft Online Community SupportPlease remember to click “Mark as Answer” on the pos...

I cannot stop Visual Studio.NET 2005 from looking for SQL Server 2005 instead of SQL 2000
I would like to know how to set SQL2000 as the default database server when running any webapplication. I have installed Visual Studio.NET and SQL Server 2000 locally in my laptop.  But anytime I want to run a webapplication I am getting the following error:System.Data.SqlClient.SqlException was unhandled by user code  Class=20  ErrorCode=-2146232060  LineNumber=0  Message="An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server doe...

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

SQL Server 2005 connection string in Visual Studio 2005
Hey I need to know what shall i specify in this connection string in visual studio web.confug file inorder to connect to ma registered server (locally) not directly to my dedicated server ....cuz i am facing network connection problems .... b4 i used to use this connection string : <add key="conn" value="data source=IP address\neel,1433;initial catalog=xxx;user id=username on the dedicated server;Password=password on the dedicated server;timeout=60"></add> So how can i change it ???? to be able to connect locally ???? Please Help ???! Thanks in advance Hiba Check out ...

AxCMS.net Award 2005 | 1st place: MSDN Professional subscription including Visual Studio 2005 and SQL Server 2005; 2nd place: new Xbox 360
We would like to introduce the 100% cost free AxCMS.net 5.2.2 - brand new release! AxCMS.net is a 100% cost free Enterprise CMS platform for architects and professional developers based 100% on Microsoft .NET. With the new download version we offer ... Improved user interface (thank you for feedback!) International support avaliable! New features (muitisite, permissions, authentication, newsletter, ...) Focused on software architects and professional developers Introducing AxCMS.net Award 2005! The AxCMS.net Award 2005 represent an opportunity ...

Sql Server 2005 Reporting Service + Visual Studio.NET 2005
Hi, Iam new 2 sql server 2005 reporting services, I created a report server project with few reports and deployed in reportserver. Everything is working fine but i want to integrate these reports to my VS.net 2005 web appliction. Through the interface i have to pass the perameters to my reports. Iam looking for help thxs in advance PradeepPradeep Reddy Take a look over here for a working example : http://www.codeproject.com/aspnet/ReportViewer.asp   HTH,Suprotim Agarwal -----http://www.dotnetcurry.com-----ASP.NET Grid With Excel Capabilities | 27 GridView Tips & Tricks |...

Building my Own Forum in Visual Studio 2005 with SQL Server 2005 Database Foundations
Hi all hope everyone is ok...Right i am about to embark on a challange greater than i have ever attempted in the past, i'm going to attempt to design build and hopeflly successfully create my own Forum. I was kind of hoping that there would be some help doing this in VB.net out there, but there isn't, and so i am asking if anyone has any suggestions, hints/tips or help they think i might find usefull, before i start getting to far into this project..Thanks for your time and effort, look forward to hearing from you all... RegardsCDG What do you mean there isn't any help for doin...

Installing .NET Framework, Sql Server 2005 Express, and Visual Studio 2005 Professional
Will someone please offer some advice, a walkthrough, or something to help me through this?  I installed all three of these pieces (.NET, SQL Server 2005 Express, and VS 2005 Professional) onto a WinXP box.  I was unable to get it to work.  It kept throwing SQL errors when I tried to run the security setup for the Personal Website Starter Kit.  So, I uninstalled everything except .NET and reinstalled.  At this point I was able to get to the setup, but unable to add users.  It doesn't throw errors or anything, it just doesn't do anything.  I really re...

How to connect SQL Server 2000 in visual studio.net 2005
How can i connect my ASP.NET web application with my sql server 2000. i used visual studio 2003, and there we drag and drop the data adapter and  then a sql connection and then use them in our application. but here we have only sqldatasource or something. so please tell me to make the connection and data retrieval procedure. Kumud Tripathi You can drag a SQL Data Source object onto you web form and select configure data source. This will give you a wizard to connect to a database, and also allows you to set up insert, delete, update statements. You can then use this datasource to...

Connot connect Sql server 2005 in visual server 2005
l can add database to my website but when l click to add a new item l get the fellow error   connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=49251"  can some one please help salis Hi,  Give this link a try http://msdn.microsoft.com/en-us/beginner/dd571502.aspx l have unintstalled both sql server 2005, visual studio 2005 and  microsoft .Net Framework then reinstalled again.and that works.  l get the ...

Migrating sql server 2005 express database to sql server 2005 database
Hi, I have an application developed using VWD and sqlserver express database. The express database is turning out to be small in size and we need to migrate to larger sqlserver 2005 database. What are the steps for this migration, please list in detail. Regards, Sandyhttp://www.thequinn.infohttp://www.sksdataservices.comhttp://www.infobasket.info Hi Sandy, To move a database from SQL Express to SQL 2005, you can use the following steps. 1. If your database file is attached to the server instance, you will need to detach it first using SQL management studio. If it is under App_Data folde...

Newbie to Visual Studio 2005
Is there a way to save only the date and not the time with it in an SQL Server 2005 database?  I have a page that allows the user to enter their birth date and save it in our database.  The problem is if the user enters 08/08/1980 the information is saved as "08/08/1980 12:00:00 AM".  The data type for the field is smalldatetime.  Is there anyway to save just the date?  I didn't have a problem with this using Visual Basic 6.  Any help would be greatly appreciated.  My code is as follows: 1 Dim dbConnection As New SqlConnection()2 D...

SQL Server 2005
I'm very confused.  I installed Visual Studio 2005 and thought I understood that SQL Server 2005 came with it, but it appears that it's SQL Server 2005 - Express.  Can anyone tell me what I need to do in order to get Data Transformation Services loaded or the equivalent of DTS in SQL Express?     beejay I have to say: actually SQL Express, not SQL2005 full version, comes with VS2005. To manage your SQL Express databases much easier, you can download a Management Studio Express from here: http://msdn.microsoft.com/vstudio/express/sql/download/ Then you can...

Visual Studio 2005 / SQL Server Management Studio 2005 Source Control?
I just want a simple software change management tool for these two tools... Team Foundation Server is too big for me...  What should I do? Hi,at the moment we still use Visual Source Safe untill VSTS ships as a RTM version. But there are other systems availble too. I heard some people that they were using CVS but I don't have any experience with it.Grz, Kris.Read my blog. Handy Firefox plugins for web developers.Workaround for non working Mark as answer buttons....

Web resources about - Visual Studio 2005, SQL Server 2005 Database, aspnetdb - Unable to Connect - asp.net.security

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Ben Carson calls for database of all foreigners in US
Boston Herald Ben Carson calls for database of all foreigners in US Boston Herald Republican presidential candidate, Dr, Ben Carson, center, ...

GOP rivals condemn Donald Trump for backing Muslim database
COLUMBIA, S.C. (AP) — Retired neurosurgeon Ben Carson said Saturday that he wants to expand the government's surveillance operations aimed at ...

Donald Trump wants "surveillance of certain mosques," database of refugees - Videos - CBS News
... rally in Birmingham, Alabama, Republican presidential candidate Donald Trump called for increased scrutiny over mosques, along with a database ...

Trump Wants A Database For All Syrian Refugees Who Enter America
Trump Wants A Database For All Syrian Refugees Who Enter America

US Republican rivals blast Donald Trump for Muslim database comments
US Republican rivals blast Donald Trump for Muslim database comments - Donald Trump said he would implement a database to keep track of Muslims ...

Animal Venom Database Could Be Boon To Drug Development
A new public database that catalogs animal toxins could spur the development of new drugs made with compounds found in animal venom.

WATCH 'TROJAN HORSE' Trump wants database for Syrian refugees in US
WATCH 'TROJAN HORSE' Trump wants database for Syrian refugees in US

Donald Trump is Under Media Assault for Suggesting ‘Muslim Database,’ But Here’s What He Says About It ...
"Donald Trump's plan for a Muslim database draws comparison to Nazi Germany"...

Resources last updated: 12/1/2015 5:58:29 AM