Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

One question I’ve seen asked a few times by people over the last few weeks is “how do I setup the new ASP.NET Membership, Role Management, and Personalization services to use a regular SQL Server instead of SQL Express?” This blog entry walks you though a few simple steps on how to-do this.

 

Quick Review: What are the new ASP.NET 2.0 Application Services?

 

ASP.NET 2.0 includes a number of built-in “building block” application services.  We call them “building blocks” because they are useful core frameworks for enabling super-common scenarios with web applications today – and as a result can provide significant productivity wins and time-savings for developers.

 

They include: a membership API for managing usernames/passwords and secure credential management, a roles API that supports mapping users into logical groups, a profile API for storing arbitrary properties about both authenticated and anonymous users visiting a web site (for example: their zipcode, gender, theme preference, etc), a personalization API for storing control customization preferences (this is most often used with the WebPart features in ASP.NET 2.0), a health monitoring API that can track and collect information about the running state and any errors that occur within a web application, and a site navigation API for defining hierarchy within an application and constructing navigation UI (menus, treeviews, bread-crumbs) that can be context specific based on where the current incoming user is in the site.

 

The ASP.NET Application Service APIs are designed to be pluggable and implementation agnostic, which means that the APIs do not hardcode the details of where data is stored with them.  Instead, the APIs call into “providers”, which are classes that implement a specific “provider contract” – which is defined as an abstract class with a defined set of methods/properties that the API expects to be implemented.

 

ASP.NET 2.0 ships with a number of built-in providers including: a SQL Express provider for going against local SQL Express Databases, SQL 2000/2005 providers that work against full-blown SQL Servers, an Active Directory Provider that can go against AD or ADAM implementations, and in the case of site navigation an XML provider that can bind against XML files on the file-system.

 

The beauty of the model is that if you don’t like the existing providers that ship in the box, or want to integrate these APIs against existing data-stores you are already using, then you can just implement a provider and plug it in.  For example: you might already have an existing database storing usernames/passwords, or an existing LDAP system you need to integrate with.  Just implement the MembershipProvider contract as a class and register it in your application’s web.config file (details below), and all calls to the Membership API in ASP.NET will delegate to your code.

 

Default SQL Express Providers

 

Out of the box, most of the ASP.NET 2.0 application services are configured to use the built-in SQL Express provider.  This provider will automatically create and provision a new database for you the first time you use one of these application services, and provides a pretty easy way to get started without a lot of setup hassles (just have SQL Express on the box and you are good to go).  Note that SQL Express databases can also be upgraded to run in the context of full-blown SQL Server instances – so apps built using SQL Express for development can easily be upgraded into a high-volume, clustered, fail-over secure 8P SQL box when your app becomes wildly successful.

 

How do I change the providers to use SQL Server Instead of SQL Express?

 

If you want to use a full-blown SQL Server 2000 or SQL Server 2005 database instance instead of SQL Express, you can follow the below steps:

 

Step 1: Create or obtain a blank SQL database instance

 

In this step you’ll want to create or obtain a connection string to a standard SQL database instance that is empty.

 

Step 2: Provision your SQL database with the ASP.NET schemas

 

Open a command-line window on your system and run the aspnet_regsql.exe utility that is installed with ASP.NET 2.0 in under your C:\WINDOWS\Microsoft.NET\Framework\v2.0.xyz directory. 

 

Note that this utility can be run in either a GUI based mode or with command-line switches (just add a -? flag to see all switch options).

 

Using this wizard you can walkthrough creating the schema, tables and sprocs for the built-in SQL providers that come with ASP.NET 2.0.  The below screens show the step-by-step walkthrough of this:

 











 

Once you have finished walking through the wizard, all the database schema + sprocs to support the application services will have been installed and configured (note: if your DBA wants to see exactly what is going on behind the covers, we also ship the raw .sql files underneath the above framework directory, and your DBA can walkthrough them and/or run them manually to install the DB).

 

Step 3: Point your web.config file at the new SQL Database

 

ASP.NET 2.0 now supports a new section in your web.config file called “<connectionStrings>” which (not too surprisingly) are used to store connection strings.  One nice thing from an administration perspective is that the new ASP.NET Admin MMC Snap-in now provides a GUI based way to configure and manage these:





ASP.NET 2.0 also now supports encrypting any section stored in web.config files -- so you can also now securely store private data like connectionstrings without having to write any encryption code of your own. 

 

ASP.NET 2.0 ships with a built-in connection string called “LocalSqlServer” which by default is configured to use a SQL Express database, and which by default the Membership, Roles, Personalization, Profile and Health Monitoring services are configured to use.

 

The easiest way to have your application automatically take advantage of your newly created SQL database is to just replace the connectionstring value of this “LocalSqlServer” setting in your app’s local web.config.

 

For example, if I created my database on the local machine in an “appservicesdb” database instance and was connecting using Windows Integrated security, I would change my local web.config file to specify this:

 

<configuration>

 

    <connectionStrings>

        <remove name=”LocalSqlServer”/>

        <add name="LocalSqlServer" connectionString="Data Source=localhost;Initial Catalog=appservicesdb;Integrated Security=True" providerName="System.Data.SqlClient"/>

    </connectionStrings>

 

</configuration>

 

Hit save, and all of the built-in application services are now using your newly created and defined SQL Server database.

 

Note: The one downside with the above approach is that I’m re-using the “LocalSqlServer” connection string name – which will feel weird if/when I deploy my database on another machine.  If I wanted to name it with my own connection string name, I could do this simply by adding a completely new connection-string, and then pointing the existing providers to use the new connection-string name in place of the default LocalSqlServer one. 

 

Hope this helps,

 

Scott

 

P.S. In some future blog post I’ll walkthrough actually using some of the above new APIs.

Published Thursday, August 25, 2005 4:17 PM by ScottGu

Comments

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, August 26, 2005 12:11 PM by Eric Newton
So whatever happened to the admin tool that can configure these things, lost from beta 1?

Being able to modify the membership/roles via the web interface (or any other) is tantamount to using the built-in aspnet providers...

So, it would be cool to see some comments on how to re-enable that tool, and what tools are available (webadmin.axd, an exe too perhaps?, aspnet iis mmc plugin?) :-)

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, August 26, 2005 1:12 PM by scottgu
Hi Eric,

You can use the MMC Admin tool to manage all of the provider settings.

You can then manage members/roles using the web admin tool (create/delete users, create and manage role groupings, etc). Note that the web admin tool will work with any provider -- so if you build your own custom provider you can point it at that and manage your settings that way too.

To pull up the web admin tool click the "ASP.NET Configuration" item in the "WebSite" menu. To pull up the MMC admin tool, just launch the IIS admin tool (inetmgr) and click the ASP.NET tab and then "edit configuration" for whatever app you want to manage.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, August 27, 2005 3:58 PM by Israel Aece
Hello Scott,

Please, why inside of the Visual Studio .NET we don't have items of the menu to have access these utilities (as aspnet_regsql.exe) or UI (User Interface), like Enterprise Library, for center these utilities?

Regards,

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, August 29, 2005 4:47 AM by nice.
Nice. Thanks for that, this was just the getting started guide I was looking for!

And Israel, that's the great thing about VS, if there isn't already a menu item, you can add one!

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Sunday, May 28, 2006 5:06 AM by ChrisS
Scott,

You mention pointing the web admin tool at your own custom providers but you didn't say how to get the web admin tool to see the custom providers.  Can you elaborate?

Thank you.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, May 29, 2006 5:56 PM by ScottGu
Hi Chris,

The Web Admin Tool uses the configured providers in the web.config file to provide an admin experience against.  So if you register things the way I described above, the web admin tool will automatically work against your remote provider.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, May 30, 2006 10:44 AM by ScottGu
Hi Cube,

That seems a little odd.  Does your app usually run ok (when not using the web admin tool)?  

Are you running any code in Session_Start that is storing information in the Session object?

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, May 30, 2006 1:30 PM by Joe Shiebler
How do you set up web pages so that the connection strings on each page are updated whenever the connection string name in the web.config file is changed? When a VWD project is hosted the database is usually on a different machine than the web pages. Before the site is uploaded to the host the strings must be changed to work in the new environ. So how to toggle between the local env and the hosted environment? Thanks,
Joe S.   joeshiebler@yahoo.com

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, May 31, 2006 2:29 AM by ScottGu
Hi Joe,

If you update the connection string within the web.config file, then any page accessing it via the connection string syntax will automatically pull the new value the next time a request comes in.  That means you can update the setting in one place and have it apply for the entire app.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, June 6, 2006 1:36 PM by Rob
Hi, sorry, can I ask a dumb question:
I have a table in my SQL 2000 db which has username/password of my users. I have gone through the walkthrough and pointed the web.config to the SQL2000 db rather than the default LocalSQLServer stuff.

So my beginners question is, at what point and where do I specify which table it is that actually holds my user info and tell .net how to correctly interpret the columns as username and password etc.

Cheers for any further insights.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, June 7, 2006 10:47 AM by ScottGu
Hi Rob,

When you specify the connection string for your SQL 2000 database, you are specifying the "Initial Catalog" name -- which is the database within SQL to use (for example: if you installed your membership tables in the "foo" database within a server you'd set the value to this).

The SQL Membership provider then looks for the aspnet_membership table for the credentials.  If you want to change this, you can modify the Provider being used.  All of the built-in ASP.NET providers are now available as source downloads -- so you have full flexibility in customizing them.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, June 8, 2006 4:55 AM by Mukesh
yes this nice but I have one problem whem used connection string name "LocalSqlServer" then work fine but when I changed name of this connetion string then I got error. My connection string is connected to Sqlserver 2000 on remote machine.Can you tell me how to change connection string name?

Thanks

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, June 8, 2006 12:43 PM by ScottGu
Hi Mukesh,

In the article above I have a sample that shows how to remove and point the LocalSqlServer connection string at a remote host.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, June 9, 2006 3:39 AM by Mukesh
Hi Scott

Thanks for replying me but I can't solve my erroor yet. this is my web.config file

 <connectionStrings>
   <remove name="LocalSqlServer"/>
 
  <add name="Cn" connectionString="Data   Source=Applications;Initial Catalog=tmpDatabase;Persist Security Info=True;User ID=mac;Password=" providerName="System.Data.SqlClient"/>
</connectionStrings>

but when i used connection name LocalSqlServer instead of Cn then work fine .

please help  me how to done this...

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, June 9, 2006 11:32 AM by Mukund
Hi...

I have two role -admin,user how to set a web.config file for allow to admin role users to access admin folder web pages and user role users can access user folder??

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, June 10, 2006 11:55 AM by ScottGu
Hi Mukund,

What you'd want to-do is to use the <location> feature in web.config to control the paths for your allow/deny tags.

For example, the below web.config entry would allow admins access to the "admin" sub-directory, and deny everyone else:

<location path="admin">
  <system.web>
     <authorization>
         <allow roles="admin"/>
         <deny users="*"/>
     </authorization>
  </system.web>
</location>

You could add a similar directive for the users directory as well.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Sunday, June 11, 2006 12:56 PM by ScottGu
Hi Mukesh,

By default the providers in ASP.NET are configured out of the box to go against the "LocalSqlServer" connection-string.

You can change this for each provider if you want to (you can lookup the schema for each provider in the web.config file).  Alternatively, the simplest way to enable this is to just change the "LocalSqlServer" connection value - in which case you don't need to change anything else.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, June 12, 2006 3:17 AM by Mukund
Thanks for reply and help me.....


Mukund

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, June 12, 2006 3:18 AM by Mukesh
Hi Scott

Thanks for reply and help me..


Mukesh.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, June 15, 2006 8:36 PM by mpfaff
Hey Scott, I have one for ya.  I use VWD 05 to design a site.  I use a hosting provider through another company.  The hosting company allows usage of a SQL Server under ASP.NET 2.0 runtime.  In VWD the website worked fine.  As soon as I loaded it, I continued to get an error saying "remote SQL server does not allow remote connections".  I have tried everything I know.  ANy ideas?  Here is my connection string:

<connectionStrings>
   <add name="Personal" connectionString="Data Source=whsql-v02.prod.mesa1.secureserver.net;Initial Catalog=DB_68408;User ID=******;Password='*******';" providerName="System.Data.SqlClient"/>
            </connectionStrings>

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, June 16, 2006 1:04 PM by J. Hemenway
Thanks for the great tutorial Scott! I was wondering though how to use your own information with the Login controls like instead of a User Name and Password, I could use an order ID for the "user name" and an email address for the "password" in a reorder system I'm working on for my website. I tried originally to import the information directly into the database but it kept crashing every time I tried. Thanks for your help in advance!

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, August 1, 2006 4:30 AM by Jules
hi, i have tried this method and it works very well. however i have a problem. the connectionstring that i now use is "LocalSqlServer" how can i do to use my old connectionstring for exple "MyConnection" without having to update every single datasource object. thanks

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, August 1, 2006 3:08 PM by Xiaohong Liu
This is really helps, thanks a lot!

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, August 2, 2006 1:56 AM by ScottGu

Hi Jules,

If you want to change your connection-string name, you'll want to re-register your membership, roles and profile providers to use a new connection-string.  You can then name this whatever you want.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, August 5, 2006 12:30 AM by Mike
In step one stated create a blank instance.. What exactly it means Can someone please explain how to setup step 1. I know how to get in the wizard and setup the rest thanks Mike

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, August 5, 2006 11:58 AM by ScottGu

Hi Mike,

To create the blank database instance you'll want to create a new Database within SQL Server.  You should be able to find a "Create Database" menu item somewhere within your SQL admin tool (the location is slightly different between SQL 2000 and SQL 2005).  You can then name the database whatever you want, and point the wizard at it.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, August 7, 2006 11:06 PM by Jim Buchan
Hay mpfaff it appears to me you are using GoDaddy? I am too and having the same problem. Have you got it figured out yet? I would like to visit with you about a solution if you have one. Thanks in advance. Jim B. jlbuchan@cox.net

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, August 9, 2006 4:54 AM by Tristan Smith
Hi Scott I've followed the advice above, my sqlserver 2000 database has been populated with tables and the Web Site Administration Tool Provider Test returns a successful connection. I can use the Add Roles and Manage Roles and I can Manage users but I can't Create users or use the Security Setup Wizard. Clicking on either returns 'An error was encountered. Please return to the previous page and try again.' I used the aspnet_regsql wizard and set it up as a sql db admin user. I added this to in the application's web.config: Any ideas?

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, August 11, 2006 2:33 AM by ScottGu

Hi Brunedito,

It could be that you installed a named instance of SQLExpress on your machine.  Could that be the problem?

Also -- have you tried creating a blank SQL Express database and accessing it to see if that works?

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, August 11, 2006 2:37 AM by ScottGu

Hi Tristan,

Is your application configured to use Forms based authentication or Windows authentication?

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, August 11, 2006 11:06 AM by Tristan Smith

Hi Scott,

It's set to use Forms based authentication. I have tried making it windows authenticated, it makes no difference, the security setup wizard still throws An Error was encountered

I've manually created an admin account in code and it appears within the Admin Tool, I can modify the settings of the user.

My current user name on the home screen is my machine login and domain. Would the location of the project have any bearing? I've got it in My Documents/Visual Studio 2005/WebSites/Sharp-Test.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, August 14, 2006 1:14 PM by ScottGu

Hi Tristan,

Can you send me an email with your web.config file?  I can then help figure out what is going on here.

Thx!

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, August 16, 2006 12:37 PM by Prasad
Hi Scott, Thanks for the post. I've been blowing up my head since yesterday trying to figure out how to deploy aspnetdb.mdf that is in app_data directory to my production server. This post not only proved me that I am stupid but also saved my time in creating an aspnetdb dB in my SQLServer2000. Thanks again.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, August 18, 2006 8:27 PM by Chris
Hello Scott, I've been trying to configure my provider so that I can have everything stored in a sql server outside my machine. I ran the aspnet_regsql.exe and it appeared that everything worked. I looked in my database and all the new tables were created, but then when I started adding new roles and members and whatnot nothing appeared in the new tables that were created in my sql server. It appears it's still storing everything in sql express? How come it's not storing everything on the server? When I open the WAT tool and click on the provider tab it doesnt give me the option to change the provider. Do you know what the problem could be? Thanks! Chris

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Sunday, August 20, 2006 3:05 AM by micky ng
Hi all, I am trying login to the state database (ASPstate) with my domain userid, using command : "aspnet_regsql.exe -U domain\userid -ssadd", but always fail. but if i use sqlserver userid, it will success to login. any one can help? thanks

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, August 21, 2006 4:20 PM by David
I get 'An error was encountered. Please return to the previous page and try again.' when I try to access the page :S

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, August 23, 2006 12:46 PM by Mukul
Following are my issues, Please Help. I have also posted this question in forums but unable to fix it. ************************************************************************** Scenario1: I was creating website on file system(c Drive) and now I have decided to move it on to local web server on my machine. The database has been created using aspnet_reqsql on to another development machine. Question: What configuration I need to change from my local setting to be able to access database on another server and the website is sitting on my local machine. ************************************************************************** Scenario2: I copied this site on to a new Windows 2000 machine and i have also installed database locally, .Net framework 2.0. My web.config localsqlserver connection string has changed similar to the ones that i created in scenario1 (editing the asp.net configuration from inetmgr). Question: I am getting this error : "Your login attempt was not successful. Please try again." Please let me know in scenario2, what configuration I need to create so as to be able to correct this problem. ************************************************************************** Thanks, Mukul

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, August 23, 2006 5:57 PM by Kenny
The images appear to me missing. The first time I visited I saw them, and now that I'm back to actually do this they are gone :)

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, August 23, 2006 6:01 PM by ScottGu

Hi Kenny,

The images are there for me right now (I assume you mean the images in the blog post above).  Can you check again -- or maybe you are having a proxy/connection issue?

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, August 23, 2006 6:58 PM by Dusty
Hi Scott, I am having a problem with my custom membership provider that I have created for a site that I have developed. I am using SQLEXPRESS(on a remote server) and I created a membership database using the aspnet_regsql.exe utility. I added my connectionstrings in the web.config file. I made sure that my membershipprovider and roleprovider has the same applicationname. When I run the application under VS 2005, I am able to login just fine with the users I created under the ASP.NET Configuration tool. However, when I deploy the application, I am unable to login with any of the users I have created. Any suggestions? Thanks, Dusty

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, August 24, 2006 12:39 PM by Kenny
Ahh I figured it out. It seems my company content filter blocked your page www.scottgu.com as 'Society and Lifestyle'. Haha, defeated by my own weapon ;) Thanks Scott!!

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, August 24, 2006 2:48 PM by Kenny
Great I got the images working but I am heavily confused... I was using SqlExpress. I would go to the ASP.NET Configuration and it would automatically create a aspnetdb.mdf file in the App_Data folder of my project. How can I get this functionality using SqlServer 2005 (full blown)? Instead of pointing to an actual DB in my default instance of 2005, I would really like it to create the ASPNETDB.mdf files in my App_Data folder again, but use full blown 2005 sql. That way my connection string can look like this: That way the db will carry with the project when I move it to our production server (which is also running a local default instance of SQL2005 full blown). Does that make sense? Thanks!

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, August 25, 2006 8:13 PM by ScottGu

Hi Kenny,

To use full-blown SQL 2005 (rather than SQL Express), you'll want to use the step in the article above to create a SQL 2005 database and point the app at it.  Unfortunately there isn't a way to have SQL 2005 use the SQL Express database in the /app_data folder.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, August 26, 2006 3:10 AM by ScottGu

Hi Dusty,

I'm pretty sure the issue you are running into above is the application name isn't set correctly.  This blog post describes the issue and how to fix it: http://weblogs.asp.net/scottgu/archive/2006/04/22/Always-set-the-_2200_applicationName_2200_-property-when-configuring-ASP.NET-2.0-Membership-and-other-Providers.aspx

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, August 26, 2006 3:12 AM by ScottGu

Hi Mike,

This blog post describes a way you can get more detailed error messages even when remotely: http://weblogs.asp.net/scottgu/archive/2006/08/12/Tip_2F00_Trick_3A00_-Show-Detailed-Error-Messages-to-Developers.aspx

You could skip the Roles check for the moment just to see what the error is.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, August 28, 2006 12:09 PM by Kenny
Thanks Scott! I got the database setup in Sql Server 2005 successfully, and the application works great... but when I go to setup my Roles I get this lengthy error msg: Unable to connect to SQL Server database. at System.Web.Administration.WebAdminPage.CallWebAdminHelperMethod(Boolean isMembership, String methodName, Object[] parameters, Type[] paramTypes) at ASP.security_roles_manageallroles_aspx.BindGrid() at ASP.security_roles_manageallroles_aspx.Page_Load() at System.Web.Util.CalliHelper.ArglessFunctionCaller(IntPtr fp, Object o) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Have you ever seen this before? Also my asp.net configuration tool runs really slow after running aspnet_regsql tool and creating the database for this application. Thanks for all your help thus far! Its been very resourceful!

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, August 28, 2006 10:40 PM by Vasu
we are group of student we want web hosting facility on ur server plz provide us ur monthly rates and conditions. and we r in New zealand. thanx

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, August 29, 2006 12:11 AM by ScottGu

Hi Vasu,

If you can send me email (scottgu@microsoft.com) I will try and connect you up with a hoster in NZ.

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, August 29, 2006 12:28 AM by ScottGu

Hi Kenny,

Do you have the connection strings setup correctly for your membership and roles setup?  What you are describing sounds like you might not have these set correctly.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, August 31, 2006 3:37 AM by krans001

hi scott,

I have made it work

thanks very much

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, August 31, 2006 10:28 PM by ScottGu

Hi Jeewai,

The issue is almost that your database is using a Beta version of the database schema.  You'll want/need to recreate the schema using the final ASP.NET 2.0 release database schemas for it to work.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, September 4, 2006 2:47 AM by Geoff B
Hi Scott, I'm getting the same error as Tristan when trying to create a new user - "An error was encountered. Please return to the previous page and try again." I am running LocalSQLServer under SQL2005 and another data file under SQLExpress. I originally has the connection configured to use a Trusted Connection, but have changed it to use login and password. Were you able to figure out what the problem is?

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, September 5, 2006 2:20 AM by ScottGu

Hi Geoff,

If you want to send me an email with more details about the error you are seeing (along with your web.config file), I can help debug it with you.  My email address is: scottgu@microsoft.com

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, September 7, 2006 4:16 AM by ScottGu

Hi Angus,

I suspect the problem might be with the connection string you are using, and specifically the security account you are connecting with.  Are you using windows integrated security to connect?  If so, then you might want to read this article to learn more about how Windows handles multiple-hops in this scenario: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000008.asp

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, September 8, 2006 5:52 AM by Angus
Hi Scott - one more thing please. How do I "point" the existing providers to use my own connection-string name in place of the default LocalSqlServer one. Thanks so much. Angus

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, September 9, 2006 6:05 PM by IanC
Hi Scott, when I try to use the ASP.NET SQL Server Setup Wizard, I get the following error when I try to connect to the SQL Server and get a list of the databases: "Failed to query a list of database names from the SQL Server. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." I get the same "Timeout expired" message when I try to add a connection in the Server Explorer of Visual Studio 2005, and my app fails when trying to connect to the Server. Enterprise Manager works fine on my computer and can access the SQL Server on the remote server, and the Server Explorer in Visual Studio .net 2003 works fine - I can see all databases, tables etc, it's just that I CANNOT get access to the server using Visual Studio 2005. What's going wrong? I am getting SOOOO frustrated with this problem, this is now my second day trying to fix it!! Please help?!

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, September 11, 2006 5:03 AM by Lee Campbell
Thanks for the tip Scott. But why was this post required. If the process made any sence at all and didnt require knowledge that 1. this is intended for SQLExpress, 2. the connection string must be "LocalSqlServer", 3. you have to run a script or comand line to get it all to work; you probably wouldt have had to write the tips in the first place. I was really expecting all of that to happen when i hit the button in VS2k5. (ie, check my conn Strings, ask which one to use or create a Db if I didnt have one, and put all the setting in to the web.config.) Im not trying to be negative, but this is a surprisingly bad implementation compared to the awesome work your team has done on so much other stuff. My 2c. Lee

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, September 12, 2006 10:47 AM by ScottGu

Hi Ian,

It sounds like you have a security configuration mis-set potentially.  Can you check to see how you are connecting to the remote machine in the VS 2005 Server Explorer?  Does it have the exact same connection string as how you are using it in VS 2003?

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, September 12, 2006 10:51 AM by ScottGu

Hi Angus,

Yep -- you can change the connection string name to whatever you want.  Simply add a new provider declaration under <membership>, <roles>, etc and point it at your new connection string name.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, September 14, 2006 4:18 AM by IanC
Scott, I'm sorry to keep sending messages, and I'm sure that this is not the right place to be asking for help, but I can't find any help anywhere else!! This is getting ridiculous! Last night, without making ANY CHANGES WHATSOEVER (I was previously working on another computer, and not going anywhere near SQL Server either), I could connect to SQL Server 2000 with Visual Studio 2005's Server Explorer, and my application worked with no delays whatsoever to open the connection to the server. I then worked on my application for a few hours and went to bed and it was all working, went to have another look this morning and I cannot connect to the SQL Server again, no matter WHAT I set the Connection Timeout to!! What's going on?! Do you have ANY ideas?! Thanks in advance, Ian.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, September 14, 2006 6:08 PM by Har
Need to know if I can configure ASP.NET 2.0 Application services to use SQL SERVER 7.0 database for membership and role management. I am still running sql server 7.0... Thanks, Har

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, September 14, 2006 8:16 PM by ScottGu

Hi Har,

Yes -- you should be able to use this just fine against a SQL 7.0 database.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, September 15, 2006 3:01 AM by ScottGu

Hi John,

Are you sure you are setting the applicationName in your config file (http://weblogs.asp.net/scottgu/archive/2006/04/22/Always-set-the-_2200_applicationName_2200_-property-when-configuring-ASP.NET-2.0-Membership-and-other-Providers.aspx)?

Also -- have you opened the database and checked to see whether data is stored in it?

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, September 15, 2006 3:05 AM by ScottGu

Hi Ian,

If you want to send me email (scottgu@microsoft.com) I can try and connect you up with someone who might be able to help investigate what is going on.  It sounds like you might have some type of provider connection issue -- which is the only reason for such a long delay.

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, September 16, 2006 2:16 AM by configure .net framework 1.1 to use .net framework 2.0
i have window server 2003 installed on my pc, have VS.net 2003 installed and also .net framework 2.0 installed, could u tell me how to configure iis to run asp.net application. thanks for helping

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, September 18, 2006 11:50 PM by ScottGu

Hi JButler76,

Here is a pointer to where you can download the shipping ASP.NET 2.0 SQL Provider source: http://weblogs.asp.net/scottgu/archive/2006/04/13/Source-Code-for-the-Built_2D00_in-ASP.NET-2.0-Providers-Now-Available-for-Download.aspx

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, September 22, 2006 12:06 AM by Nachi
Hi Scott I am running Sql 2000,VS 2005 remote server instance locally. I removed LocalSqlServer tag in machine.config and followed the steps. my web.config holds this data I still get error saying "Server Error in '/asp.netwebadminfiles' Application." Unable to serialize exception. Its using InProc state. Please advise anything I should change

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, September 22, 2006 9:26 AM by Derek
Scott, Do you see any issues in trying to leverage these tables when programming a regular Windows Form app? Our company might need to utilize both Web and Win applications and if we can leverage one central store and Windows Authentication with roles stored in SQL, that would be the complete package. Let me know if you have encountered this situation before (I'm sure you have) and any best patterns and practices you may be able to point me to....Thanks

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Sunday, September 24, 2006 12:14 PM by ScottGu

Hi Derek,

You could use these tables with a WindowsForms application.  In fact, with the next release of Visual Studio we are looking to provide an API that makes doing so easy.

What I would recommend for now is to build a custom set of web-services that expose the membership/roles APIs for you to use remotely from a WinForms client.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Sunday, September 24, 2006 2:41 PM by Daniel Spitale
thanks ! ! ! ! That was just what I was looking for !

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, September 25, 2006 9:13 AM by Derek
Thanks Scott. I just have one follow-up question. In ASPNet, most security models recommend accessing the DB's using one least privelaged account. If we were to use the same security tables in WinForms as used in ASPNet (previous question), does it make sense to have our WinForm users access the DB's using one specific account, or is that not a good practice? We'd be using Windows NT Authentication with the roles stored in the SQL Server ASPNet tables (not SQL Server roles). I'd like to keep the models as close as possible, but if 'Best Practices' say you should keep a SQL Server login for each user when creating a WinForm App, then that's how we would want to implement our environment here.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, September 25, 2006 9:56 AM by ScottGu

Hi Derek,

I'd recommend not having your WinForms applications even connect to the database directly.  Instead, I'd recommend building a web service API that runs on the server and accesses the tables - and have the WinForms clients access those.

That will protect your database better and give you one extra level of control.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, September 25, 2006 10:28 AM by Eric
I have an ASP.NET 2.0 application which has been completely developed in VS2005 on my local machine in IIS. Now that I have moved the application to a production server and the database connection string has been switched over I am having problems connecting with the SQL Server 2000 database. One day it was running for probably 5 or 6 hours smooth and all was well at which point I was using the database server IP Address in the connection string. Later I started to receive the below message; I changed out the IP for the fully qualified name for our database server and all was well again; for about 24 hours. I came back in again today and tried to run the application just to be sure all was running smooth and was once again confronted with the below error. I tried moving back to using the IP, but this time that change had no result on my ability to connect to my data source. Server Error in '/GDR' Application. -------------------------------------------------------------------------------- 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, September 25, 2006 11:32 PM by ScottGu

Hi Eric,

Can you send me email with more details about the issue you are seeing? I can then loop in some folks who should be able to help.

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, September 26, 2006 3:18 AM by Kumar
Hi Scott In relation to your Example, I am using StateServer as the Session State mode. When I run Asp.Net config through VS 2005 it says server error unable to serialize....Any ideas.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, September 27, 2006 12:40 AM by ScottGu

Hi Kumar,

Can you send me an email with your exact error message?  I can then help debug it.

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, September 28, 2006 3:29 PM by Mark
What am I doing wrong? Can I during dev. with VS2005 use sql2005 for my sqlMembershipProvider? I ran the utility and created the tables, views and sp's. Have verified it's not a connection issue (use other tables within db without issue). Web config: When I run aspnetcfg it creates aspnetdb locally no matter what I do. I must be missing something, but unsure as to what. Running sqlexpress locally, but from what I read I should be able to make this work. Thanks

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, September 28, 2006 5:21 PM by Mark
Ok, figured out from my last post to remove LocalSqlServer and reAdd it. However this seems lame. I should be able to call it whatever I want. I develop for multiple sites and configurations and don't want to redefine what's in the machine config file, unless I'm not understanding this correctly. Can you explain how I can do this? Thanks

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, September 29, 2006 12:53 AM by ScottGu

Hi Mark,

You don't have to use the LocalSqlServer connectionstring name.  You can register any connectionstring name you want, and then simply add providers to <membership> or <roles> or any other application service and point at your new connection string name.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, September 29, 2006 1:56 PM by Mark
Sorry to be such a pain with this, but it is/was your blog here that got me the closest to making this work and it now does because of your help! Ok, I found I can totally not worry about 'LocalSqlServer', just simply remove the auto-created default providers and recreate them using a different connection string and all seems well. Wow! why was that so hard to understand or I still didn't get it??? Heres what I got now and seems to work. Thanks again.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Sunday, October 1, 2006 9:44 PM by ScottGu

Hi Brian,

The problem you are having is that the worker process that you are running IIS under (which is the "NETWORK SERVICE" account doesn't have security permissions to access the SQL database).  You'll want to go into the SQL admin tool and grant access permissions for that account to fix this.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, October 12, 2006 11:11 AM by Randy
Hello Scott, Is giving the "Network Service" account access a secure solution? Does this present a security issue to the application, especially if it is used by external clients. Thanks, Randy

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, October 12, 2006 1:50 PM by ScottGu

Hi Randy,

Network Service is the account that worker processes within Windows use.  It doesn't actually allow people on the network to access it (in fact - it can't be logged in remotely).

So you shouldn't need to worry about external clients accessing a file ACL'd with it.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, October 12, 2006 10:29 PM by Notso Evil
Are the letters d b o hardcoded into the Framework? I cannot seem to find any info on how to use these items in 2005 so that the tables and procs are prefixed with anything other than dbo. My dba group won't allow us developers dbo access to databases. So, I ran the aspnet_regsql.exe tool with the -sqlexportonly option. Did a search and replace on [dbo] to [mySchema] and had the dba's run the script. Now when I do anything like Membership.CreateUser or click on the 'security' tab in the website admin tool I get... Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, October 14, 2006 11:28 AM by ScottGu

Hi Notso,

Can you send me email repeating this question?  I will then loop someone else in on the email thread who might be able to help.

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, October 17, 2006 10:50 AM by ScottGu

Hi Polly,

The raw .SQL files for generating the schema can be found in this directory:

c:\Windows\Microsoft.NET\Framework\v2.0.50727

You could try using those directly to create the schema in your existing database.  

Alternatively, you might also want to checkout this blog post I did a few days ago: http://weblogs.asp.net/scottgu/archive/2006/10/13/Tip_2F00_Trick_3A00_-Source_2F00_Documentation-for-Simple-ASP.NET-2.0-SQL-Providers-Published.aspx

It points to some simplified schema table providers that you can alternatively use.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, October 17, 2006 11:57 AM by JeffNBoston
Hi Scott, I am having a similar issue as Notso Evil above. I am trying to implement the provider model on a shared hosted website (www.1and1.com) and use the asp.net 2.0 login control, etc. The dbo username is assigned by the host company and is something like dbo123456789. Therefore the aspnet_regsql files and perhaps System.Web.Security.SqlMembershipProvider reference "dbo" I could edit all the views and stored procedures changing "dbo" to "dbo123456789", but am not sure this would solve my problem. Also, it appears that SqlMembershipProvider attempts to execute this statement "System.Web.Security.SqlMembershipProvider.CheckSchemaVersion(SqlConnection connection) +85" which probably causes the error "Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'." I am looking for an easy way to implement the provider model on my hosted site. Any help would be appreciated. Thanks, Jeff

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, October 18, 2006 4:58 AM by Polly
Hi Scott, thanks for the brilliant links, and all invaluable your time. Kind regards, Polly

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, October 20, 2006 12:01 AM by ScottGu

Hi Jeff,

Apologies for the delay in getting back to you.  I got your email as well and will try and get back to you this weekend.

Sorry!

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, October 24, 2006 10:08 AM by gra
great help. Had been searching for this for half a day. It all works. thanks

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, October 28, 2006 12:35 PM by ScottGu

Hi Barry,

Can you send me an email (scottgu@microsoft.com) describing this more?  I can then help you get this configured and setup.

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, October 30, 2006 6:32 AM by Barry
Thanks Scott I've sent you a mail - if you need any more details please let me know.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, October 30, 2006 8:47 PM by Joseph Baggett
I was interested in being able to alter the aspnet_membership table. I've come accross the need to add or delete columns to membership and have not been able how to do it. I end up just building my own tables for users/roles, etc. One thing I noticed is I cannot deploy the sql server database on a shared hosted server without the built in tables. So right now I basically got tables I do not not programatically use. I am sure you could just add a relationship from another table, but I was wondering if anyone had a good way to be able to modify user information easily using the built in services?

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, November 15, 2006 5:41 AM by John

Thanks Scott, I was realy wondering abt keeping the Membership in SQL Server 2000

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, November 27, 2006 5:34 PM by Mark

Scott,

Is there a way to set the connection string for the provider at runtime?

Thanks

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, November 29, 2006 1:09 PM by ScottGu

Hi Vamsi,

Can you send me email (scottgu@microsoft.com) with more details about this issue?  I can then investigate and help.

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, November 29, 2006 11:30 PM by ScottGu

Hi Ben,

I wonder if you might be running into this gotcha that I blogged about here: http://weblogs.asp.net/scottgu/archive/2006/11/20/common-gotcha-don-t-forget-to-clear-when-adding-providers.aspx

Can you try this suggestion out and see if it fixes in?

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, November 30, 2006 6:00 PM by Vince

Great work on this blog.  I am finally connected to my SQL Server 2000 database and all my provider info in the web.config is apparently working.  

Question 1.  Can I delete the appdata folder now?

Question 2.  When I Remote Desktop into my Development machine (Windows XP) across my wireless network,  there seems to be some  permission problems.  ASP.Net Configuration tool does not run and when I run my pages (login and create user), they do not work.  I get some error about not being able to create the User instance.

Thanks for your help in advance

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, December 1, 2006 9:25 PM by Robert

I've got a couple of questions about security.  By default, all of the tables are created under the dbo schema.  If my plan is to write a highly secure web application, will this pose any sort of security problems for me later on down the road?  I would set up a new user and grant it read/write access to the dbo.aspnet* tables.

It is my understanding that the only way to change the schema the providers use is to re-write the providers myself.  While it would probably be a great excersise, I need to concentrate on writing my application, rather than rewriting the tools already provided to me.

Also, are the provided Login controls protected against SQL injection attacks?

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, December 2, 2006 6:24 PM by tony cox

<add name="LocalSqlServer" connectionString="Data Source=DDMBBJB05; Integrated Security=True;Initial Catalog=DD_InventoryControl;User ID=*******;Passowrd=*********"  

Just a tiny one - Password is incorrectly spelt?   Was this a cut'n'paste - if so then maybe thats the root of the login failure - ie not authenticated.  Just a thought.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, December 2, 2006 9:40 PM by ScottGu

Hi Robert,

The schema doesn't require the database account to run with DBO permissions.  So you can connect using a normal security account and not have to worry about elevation.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, December 5, 2006 4:34 PM by Jake

Scott,

This was a very useful article for me.  Thanks for the good information.  

Can this application services database be setup within an existing database?  This is a scenario where the site is hosted by a 3rd party, and we can have only one database for the site.  

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, December 6, 2006 12:11 AM by ScottGu

Hi Andrew,

I'm going to be writing this hosting migration tutorial in the next two weeks.

We are actually going to be releasing a new automated tool soon that will help with this.

thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, December 6, 2006 4:37 AM by lalit pandey

First, import the "System.Data.OleDb" namespace. We need this namespace to work with Microsoft Access and other OLE DB database providers. We will create the connection to the database in the Page_Load subroutine. We create a dbconn variable as a new OleDbConnection class with a connection string which identifies the OLE DB provider and the location of the database.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, December 6, 2006 3:14 PM by Andrew

Scott,

Great!  I look forward to the article.  If you need someone to review the article and try the steps, I'd be happy to.  I could tell you useful info like "yes, I followed all the steps with no problems", or "this part presumes knowledge that entry level web programmers don't have", etc.  In any case, I'm looking forward to it.

Andrew

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, December 8, 2006 12:33 AM by ScottGu

Hi Paul,

I believe the problem you are running into above is that you have registered a membership provider, but not declared a provider for the roles provider.  So when you call Roles.CreateRole() it fails - since it is trying to connect to the default SQL Express provider.

Can you add a role provider to your web.config file (clearing the previous one like you do for membership) and then try again?

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, December 8, 2006 4:40 AM by Paul (Guernsey, UK)

Scott, many thanks for your response.

I have added the following code to the web.config:

<roleManager enabled="true" >

     <providers>

       <clear/>

       <add name="AspNetSqlRoleProvider" connectionStringName="ConnectionString"

           applicationName="/"

        type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

     </providers>

   </roleManager>

Seems to be working perfectly now. Thanks you once again for your assistance.

Regards

Paul

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, December 8, 2006 10:58 AM by barber0306

I'm having the same problem that IanC had (September 9).  Does anyone know how he solved that problem?  I've been racking my brain trying to solve this.  

Amit

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, December 9, 2006 7:14 AM by yasminnnnn

hey

i can not find and run the aspnet_regsql.exe file..what could be the problem?

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, December 9, 2006 3:30 PM by Amit

Success!  I was able to solve my problem.  For anyone else thats having this problem, I had to inject a Connect Timeout in the prompt that asks for your server name.  For instance, my server name was Nightcrawler so where aspnet_regsql was asking for my server name I had to write, "Nightcrawler; Connect Timeout=180"  aspnet_regsql then had no problems connecting to my remote machine.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, December 13, 2006 8:25 AM by Debasish

Which custom providers are generally pushed into machine.config?[Membership / Profile / RoleManager] entries for sql can be found at machine.config.

I think listing a cuatom provider in machine.config helps VS.NET 2005 in discovering the providers in the admin mode.

Please clarify

Thanks

Debasish Bose

Oracle Corp.

# Using different schema other than 'dbo' for membership,roles mechanism

Tuesday, December 19, 2006 1:50 PM by Anjan Bhowmik

I have created sql server 2000 database. Used "LocalSqlServer" connection to point to it. Used built in membership,roles mechanism to manage my users. It worked fine on my development machine but when i uploaded the database to the hosting server via Sql Web Admin tool, i fail to use the membership, roles mechanism. the Exception i get is "Invalid objectname 'dbo.aspnet_SchemaVersions'." I checked and found that in the hosting database the "aspnet_SchemaVersions" table has different owner other than "dbo" , thats why dbo.aspnet_SchemaVersions cann't be found. My question is can i use schemas other than 'dbo' to create the aspnet* tables and stored procedures to use membership,roles api? Plz help.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, December 19, 2006 8:23 PM by Juvan

Hi,

Could you inform me please were did you get that window when you were metioning the third step "Step 3: Point your web.config file at the new SQL Database"

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, December 19, 2006 9:29 PM by ScottGu

Hi Anjan,

The good news is that we have a tool coming out shortly (either today or tomorrow) that will help with hosting the ASP.NET DB schemas in a shared hosting environment, and prevent you from running into DBO permission issues like you described above.  I'll be blogging about this later this week - so check back on my blog for details.

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, December 20, 2006 12:51 PM by ScottGu

Hi Juvan,

The window I showed in step 3 was from the IIS admin tool (which includes a GUI that supports connection string management).

Alternatively, you can just open up the web.config file directly and configure it there.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, December 22, 2006 7:58 AM by Anjan Bhowmik

Hi Scott, will that tool ever come? I urgently need that or any other way to make membership system work on a shared hosting database. I have to do this within 4 days from today. So , plz if u can help.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, December 22, 2006 12:30 PM by ScottGu

Hi Anjan,

I just blogged about this new tool here: http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx

It should help with deploying your membership data to your remote hoster.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, December 28, 2006 9:23 PM by Daniel Harker

Hi,

I'm trying to do something slightly different on my setup here. SQL Server 2005 Express was installed for me by a third party app and I want to connect to that.

When I ran the ASPNET_REGSQL utility I was not able to connect to the SQL server, despite the fact that I could connect using the SQL Management Studio Express. I then found that it worked perfectly if I put the named pipes name in to the server name box.

I'm now having trouble modifying the connection strings for it. (I'm trying to use WebParts by the way).

I've placed this in to my web.config:

<connectionStrings>

   <remove name="LocalSqlServer"/>

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

 </connectionStrings>

but it doesn't work, says it cannot connect.

I also tried modifying the value in the IIS snap in to:

data source=.\HELM;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true

(so basically putting in the correct instance name)

The error I get in the second case above is:

An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 1802 and the SqlException message is: CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file 'C:\INETPUB\WWWROOT\WEBARTS-0-0-8-0\APP_DATA\ASPNETDB_TMP.MDF'.

Creating the ASPNETDB_d6399dae01f648d292811428a592b7dc database...

Can anyone help?

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, December 30, 2006 11:21 AM by ScottGu

Hi Daniel,

Can you send me an email (scottgu@microsoft.com) with more details about this problem?  I can then help you get it working.

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, January 8, 2007 11:18 AM by James

Hi Scott

I am using asp.net 2 and sql server 2000, which i've set as membership, personalization and role provider, and forms authentication. It all works fine in debug mode, but cannot log in through an anonymous browser. I tried checking off permissions on the db, but still no access. Any clue?

thanx

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, January 10, 2007 2:15 AM by ScottGu

Hi James,

What does your database connection string look like?  Does it use Windows authentication or SQL credentials to connect?

These two articles should help walkthrough how to connect to SQL using each approach:

http://msdn.microsoft.com/library/en-us/dnpag2/html/paght000008.asp

http://msdn.microsoft.com/library/en-us/dnpag2/html/paght000010.asp

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, January 11, 2007 12:48 PM by JP

Hi Scott,

I'm getting the error:

"Login failed for user pc01\ASPNET" when accessing the database created on sqlserver express. What kind of permissions should this DB need?

Thanks in advance

My conn string is:

   <connectionStrings>

       <remove name=”LocalSqlServer”/>

       <add name="LocalSqlServer" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=mydb;Integrated Security=True" providerName="System.Data.SqlClient"/>

   </connectionStrings>

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, January 12, 2007 12:20 AM by neo

Hi, Scott

 How can i get all user informations after login on other pages (User ID , Email....)?

 I'm using Login Control and build my owner membership provider.

Thanks

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, January 12, 2007 2:01 AM by ScottGu

Hi Neo,

Scott Mitchell has a great series here that I recommend: http://aspnet.4guysfromrolla.com/articles/120705-1.aspx

It covers how to use the Membership support in more detail.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, January 12, 2007 2:08 AM by ScottGu

Hi JP,

You'll want to make sure that the ASPNET worker process account has read and write ACL access to the SQL Express database file, and the /app_data directory it is contained within.

You can do this by right-clicking on the app_data folder and then grant the ASPNET worker process account permissions to it.

Hope this helps,

Scott

# Timeout when using SQL Server Management Studio

Friday, January 12, 2007 2:48 PM by Zvi

I get the following message when trying to connect to a remote SQL 2000 server with SQL Server Management Studio: "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)"

Works perfectly well when I connect remotely to the SQL Server instance with Enterprise Manager. Any ideas and assistant of what may be causing the different experience will be greatly appreciated.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Sunday, January 14, 2007 4:57 PM by ScottGu

Hi Zvi,

My guess is that there might be some port conflict in terms of configuration - since timeout errors usually mean the tool can't connect at all.

What is your configuration?

thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, January 16, 2007 5:25 AM by Daniel

Hi Scott,

Great post... but of course I'm here because I've had a problem.

I've used an SQL 2000 db and followed your instructions, however while the ASP.NET Config site works perfectly (I can create accounts, manage roles etc) I cannot log-in using my login page.

Stranelgy, if i use the CreateUserWizard to create an account on my site the user is created and logged in... So my DB/application relationship would seem to be correct.

My login page just contains an unaltered login control. Nothing fancy...

When I log in I just keep getting the 'Login Failed' message.

And yes, I've checked CAPS aren't on! ;)

Am I missing something basic?

Thanks Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, January 16, 2007 5:37 AM by Daniel

ARGH!!!!!

Problem solved...

I noticed I had a Login1_Authenticate module that I didnt remember writing... I must have doubled clicked on the control.

No wonder the login wasn't working. It was going to the empty module.

Anyway, great post Scott.

Shame you have to deal with the rest of us bumbling fools.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, January 23, 2007 4:13 AM by Kim

Hi Scott,

I have read this and hundreds, yes hundreds of posts concerning connecting to Sql server express outside of the Visual Studio ide. I have tried numerous walk throughs and WebConfig strings from many forums without success. Before I toss in the towel and start looking for alternatives as a back end for a small web application that works in VS, am I missing something or just wasting time trying to deploy sql express. I do not want to ramp up to full Sql server. It would seem from all the posts on google the express version is far from easy to connect to when deployed in ISS.

Any unbiased comments appreciated

Thanks a helpfull site

Kim

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, January 23, 2007 10:36 AM by ScottGu

Hi Kim,

Can you send me email (scottgu@microsoft.com) with the specifics of what you are trying to-do?  I'm not entirely sure I understand your scenario.  Are you looking for a connection-string that allows you to connect to a SQL Express database?  Or are you looking for a away to "upsize" a SQL Express database to SQL for a hosting environment?

Send me an email with more details and I'd be happy to help.

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, January 24, 2007 2:48 AM by Kim

Thanks for responding Scott,

My apologies for the frustrated post, I think I may have finally resolved my connection woes with Sql Express by switching to Sql Authentication, creating a user and using those creditials to create a new data connection within Visual Studio Server Explorer.

I then tested a SqlDataSource on one of my applications forms using this replacement connection and it worked when run under IIS in IE. I will send more details in a followup email, after more testing.

Thanks again Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, January 24, 2007 9:55 AM by Tiran Hay

Scott! you are simply the best!

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, January 24, 2007 11:51 AM by ScottGu

Hi Kim,

That sounds like the fix - I bet your SQL server was not allowing your IIS account to access it using Windows Authentication.  You can either use SQL Authentication, or go into the SQL Manager and grant the IIS account access to the database.

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, January 27, 2007 9:24 PM by Harold

I was wondering; Is there a way to link multiple membership datastores to create a hierarchy where a global datastore role could be a member of a subsystem's datastore role?  As in a AD structure where a Domain Admin group or role can be added to a local machines Admin group (role).  Can this work with the 'Out of the box' Membership provider or would I have to create a custom provider to accomplish this?

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, January 30, 2007 12:37 AM by ScottGu

Hi Harold,

You can do this with the ActiveDirectoryMembership provider if you are calling into an Active Directory in a star/tree configuration (meaning it already aggregates users in a domain).

If you want to aggregate across multiple separate stores, then you'd need to create a custom membership provider.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, January 31, 2007 1:49 PM by Harold

Thanks Scott!  By the way, impressive blog!!

Harold

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, January 31, 2007 3:58 PM by sankar

Hi Scott,

I posted my problem in forums and they redirected me here and yet i am not clear. My problem is,

I need to connect to a test database server's aspnetdb database...When i try to open the website administration tool's security tab i get this error.

The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.

I tried to open the aspnet_SchemaVersions and the table is empty...

would running the command

aspnet_regsql.exe -E -A all -S servername

again solve this problem?...

your help is very much appreciated.

Thanks,

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Sunday, February 4, 2007 7:05 PM by ScottGu

Hi Sankar,

I believe what is happening is that you have an older ASP.NET Beta schema installed on that database.  Can you delete the schema and make sure you are running the aspnet_regsql tool that shipped with ASP.NET 2.0 (the final release) to recreate it?

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, February 9, 2007 10:01 AM by Alexander

Hi, Scott,

I'm trying to start working with profiles in ASP.NET so I've created aspnetdb database via aspnet_regsql and it was successful. Now I try to select a provider via ASP.NET Web Application Administration tool. And although connection string in machine.config appears to be correct

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

but i keep getting "Could not establish a connection to the database" message when hitting "test" href.

Even if I add section to web.config file:

<configuration>

   <connectionStrings>

       <remove name="LocalSqlServer"/>

       <add name="LocalSqlServer"

        connectionString="Data Source=localhost;

          Initial Catalog=aspnetdb;

          Integrated Security=True"

          providerName="System.Data.SqlClient"/>

   </connectionStrings>

</configuration>

- the error is still the same... :(

p.s. DB is really created and I use MS SQL Express server that is running and its tables are visible via Server explorer in VS 2005

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, February 10, 2007 1:46 PM by ScottGu

Hi Alexander,

Are you running this using IIS or the built-in VS web-server?  If you are running with IIS, then you need to make sure that the ASP.NET/IIS worker process account has access to the SQL database you are trying to connect to.

Thanks,

Scott

# re: Appropriate Permissions to Roles DB

Saturday, February 10, 2007 4:04 PM by Dan Clem

Hi Scott,

I've read all posts fairly thoroughly without specific answer. I act as both DBA and developer and usually do corporate intranet apps, so I want to confirm I'm configuring security properly on the database.

If using Forms authentication, do I simply need to grant the anonymous IIS account EXEC permissions on the stored procedures? Does it need to be a member of the data reader or data writer SQL Server roles?

If using Windows authentication, do I simply create a SQL Server group and give them the above permissions, then add each Windows account to this group?

Thanks as always.

Dan

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, February 12, 2007 4:15 AM by Alexander

Hi, Scott,

i've tried the same thing on my home notebook, where i had windows xp home sp2 and there were no  any problems with it - i used all default settings for localhost. As you know there's not IIS on home edition.

Ok, here i have windows xp professional and IIS really HAS BEEN installed - i guess not correctly, so i removed it. I had suspicion, that IIS in some way interfere with VS web server, but now it doesn't work anyway! :( Please, advice me something...

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, February 12, 2007 1:09 PM by Alexander

Hi, Scott,

i've found the problem. Something wrong with password. When I run sqlexpress server in 'local system mode', i.e. without password - connection is successful. But it's not correct all in all - I want to use windows authentication (i am a part of domain in the office). But my windows login/password doesn't work, when i try to indicate it. And I don't have 'domain' textbox there, although i should have it, according to MSDN...

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, February 13, 2007 1:03 AM by ScottGu

Hi Alexander,

The windows identity you'll use when connecting to the SQL database will be the process identity of the worker process in IIS - and not your own windows identity.  

Can you open up the SQL Database Admin tool and make sure that the IIS account has access to the database?

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, February 17, 2007 8:52 AM by Azlan

hello Scott, i'm having the same problem with Alexander. Is the SQL Database Admin tool you refer to is SQl Server Configuration Manager( I use SQL server 2005)? If so what do i need to tweak...?

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, February 17, 2007 11:20 AM by Anthony

Hi Scott,

I'm using SQL Server for Role Management and the WSAT tool seems to be working correctly. But I'm getting the same error message as Brunedito: "Cannot open database "aspnetdb" requested by the login. The login failed." If it's because of a named instance of SQL Express on my machine, how do I remove/remedy it??

Using SQL Config Manager, I stopped the SQLExpress service and now get the error message: "System.Data.SqlClient.SqlException: Cannot open database "aspnetdb" requested by the login. The login failed."

Any help appreciated!

Anthony :-)

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, February 17, 2007 8:54 PM by ScottGu

Hi Anthony,

Are you using a SQL Express or a SQL Server database?  If SQL Server, how did you configure the providers within your web.config file?

Here are two common issues that people run into when registering new providers that you want to watch out for:

http://weblogs.asp.net/scottgu/archive/2006/11/20/common-gotcha-don-t-forget-to-clear-when-adding-providers.aspx

http://weblogs.asp.net/scottgu/archive/2006/04/22/Always-set-the-_2200_applicationName_2200_-property-when-configuring-ASP.NET-2.0-Membership-and-other-Providers.aspx

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, February 17, 2007 8:55 PM by ScottGu

Hi Azlan,

Can you provide more details on the error you are running into?  I'm not sure which of the issues above you were referring to.

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Sunday, February 18, 2007 1:54 PM by Adam

You know, I think I see why this is happening, especially if I choose to only export my schema. If I only export my schema, then the aspnet_SchemaVersions table does not get populated  with necessary rows for the CheckSchemaVersions stored procedure to verify.  

This is a pain, because I want to deploy my app and database fresh, with no data, just the schema.  But the Sql Database Publishing Wizard doesn't give you the option to selectively choose which tables you want to script to just the schema or schema and data.

So, I guess the best solution would be to just disable this schema checking altogether, so I can just export my local database schema only, and have it work.  If I modify the CheckSchemaVersion sp to always return a successful value, will that do the trick?

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Sunday, February 18, 2007 10:46 PM by Azlan

sorry Scoot, noob error i didn't modify my web.config file. i was wondering where did the password we set in the web site administration tool is stored? I can't find it in the aspnet_users table? If so hw can i unhashed it?

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, February 19, 2007 10:58 AM by ScottGu

Hi Azlan,

Glad you got it working.  When you create new users the password is one-way hashed and stored within the database.  This means that even if the database is compromised, hackers can't reverse engineer the origional password.

Hope this helps,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, February 19, 2007 11:05 AM by ScottGu

Hi Adam,

Is there a reason why you can't just re-create all of the tables that the aspnet_regsql tool creates on your remote host?  This might be the easiest approach, and they don't consume much storage so I don't think there is any performance or storage reason not to re-create them.  This would probably simplify life considerably if you could just keep them (but not use them).

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, February 22, 2007 2:30 AM by matheui

i've just trying out the your tutorial and i would like to expand it a bit. I wanted to integrate dropdownlist with role admin. Each role won't get the same values in the drop down list. do u have info regarding of this or some links that i can study?

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, February 24, 2007 8:04 PM by adamtoth

Hey Scott,

My database is a mixture of the aspnet_regsql  tables and my own tables.  In my dev and stage environments, I've got a bunch of garbage data in there, that I don't want to publish up to the production server on a clean "from scratch" publish. The Database Publishing wizard doesn't give you an option to publish certain tables with schema only and others with schema and data, it's all or nothing.

So, if I want to publish just the schema, then I end up missing the schema version rows that the aspnet_regsql script creates.

So, what I ended up doing was just altering the stored procedure that checks the schema version, and made it always return the successful return value.  This worked like a charm, now I can export schema only and don't get the "requires a database schema compatible with schema version '1'" error anymore. HTH someone else out there with a similar issue.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, February 24, 2007 8:04 PM by adamtoth

Hey Scott,

My database is a mixture of the aspnet_regsql  tables and my own tables.  In my dev and stage environments, I've got a bunch of garbage data in there, that I don't want to publish up to the production server on a clean "from scratch" publish. The Database Publishing wizard doesn't give you an option to publish certain tables with schema only and others with schema and data, it's all or nothing.

So, if I want to publish just the schema, then I end up missing the schema version rows that the aspnet_regsql script creates.

So, what I ended up doing was just altering the stored procedure that checks the schema version, and made it always return the successful return value.  This worked like a charm, now I can export schema only and don't get the "requires a database schema compatible with schema version '1'" error anymore. HTH someone else out there with a similar issue.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Tuesday, February 27, 2007 7:08 PM by CaffeineFixx

Hi Scott,

Am attempting to run aspnet_regsql.exe to install the tables and storedprocs on to an instance of SQL Server 2000.

However, the process fails with an error code of 515 and I have been able to find very limited information to assist in understanding/resolving this.

The text of the error message reads:

Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_Permissions____0000000026', column does not allow nulls. INSERT fails.

Both GUI and command line give the same response so I'm confident it's not a typographical error.

I am using the sa account credentials when running the program so wouldn't have thought that permissions would have been an issue.

I would be grateful if you could shed any light on what might be going on with this.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, February 28, 2007 2:06 AM by NavKrish

Hi Scott,

The article was nice helping. And i 've a query. I need to have the backend for my personalization,membership and role provider to be a another DB, rather that SQL Servers(Say DB2).

Any idea, how to incorporate to have another database for my personlization activities.

Thanks for your views.

Regards,

NavKrish

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Wednesday, February 28, 2007 3:51 PM by CaffeineFixx

Hi Scott,

Would be grateful if you assist me with an issue I am having when attempting to install the ASP.NET application services to an instance of SQL Server 2000.

After selecting the target server and database the subsequent step fails with a SQL error number 515. The process trying to execute is InstallCommon.sql

SqlException message is:

Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_permissions_____0000000001A'; column does not allow nulls.

The same error occurs if running from command line or GUI, or from the local machine or remote.

I'm using the sa account so wouldn't have thought that permissions were an issue.

Thanks in advance for any insight that you can provide.

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Thursday, March 1, 2007 3:40 PM by Sp

hi scott,

thanks for article, but am having problem. I ran aspnet_regsql and using exisiting DB to store necessary table and sprocs. have following in my web.config

   <connectionStrings>

       <remove name=”LocalSqlServer”/>

       <add name="somename" connectionString="server=sqlsvrname;Initial Catalog=existingdbname;UID=appuser;Password=apppwd" />

   </connectionStrings>

<sessionState mode="SQLServer" allowCustomSqlDatabase="true" sqlConnectionString="data source=sqlsvrname;database=existingdbname;user id=appuser;password=apppwd" cookieless="false" timeout="40"/>

getting following err msg

'Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server. Please install ASP.NET Session State SQL Server version 2.0 or above'

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Friday, March 2, 2007 1:43 AM by jpallon

Hi Scott,

I'm using SQL server 2005.  I've followed the steps and the Website Admin Tool is working perfectly on my local machine.  However, I would now like to connect to a remote server.  I'm still following the steps by changing my web.config file so that it would point to the remote server, but I keep on getting this error whenever I try to test the provider connection: "Could not establish a connection to the database. If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider."

This is my web.config:

<connectionStrings>

<remove name="LocalSqlServer"/>

<add name="LocalSqlServer" connectionString="Data Source=DATAWAREHOUSE;Initial Catalog=aspnetdb;Integrated Security=SPPI; User ID= ******; Password= *******" providerName="System.Data.SqlClient"/>

</connectionStrings>

Can you help me on this? By the way, the remote server uses SQL Server 2005 and I've already setup the aspnetdb along with my SQL login credentials.

Thanks and hope to hear from you. :-)

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Saturday, March 3, 2007 10:26 PM by ScottGu

Hi Sp,

Typically that error means that the schema wasn't correctly installed in that target database location.

Can you double check that it was set correctly?

Thanks,

Scott

# re: Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

Monday, March 5, 2007 9:11 AM by Bob

Hi Scott

Is there any way to export the whole database to a remote server like a shared hosting.

I can export the database tables and views using databse export wizard in SQL server 2005. but this does not include other objects like stored procedures and schemas.

if you have answered this question already and I missed the post I apologize in advance.