Configuring an ASP.NET 2.0 Application to Work with Microsoft SQL Server 2000 or SQL Server 2005

Over the past couple of weeks, I saw a number of posts on the Wrox Programmer to Programmer forum (p2p.wrox.com) asking how to configure an ASP.NET 2.0 application to work with SQL Server 2000 or 2005. Since this is not an easy undertaking, and requires knowledge about ASP.NET 2.0, IIS and SQL Server, this article might help to familiarize you with some of the important concepts that you need to be aware of

Introduction

Configuring an ASP.NET 2 application to work with SQL Server is not an easy undertaking. You need to be aware of a number of security related concepts in the area of SQL Server, IIS and ASP.NET 2.0. The number of different options you have make the problem even more complex. In this article, I describe some of the common scenarios that you are likely to run into when working with SQL Server and ASP.NET pages. I'll be using SQL Server 2005 in all my examples, but most of the concepts also apply to SQL Server 2000. Note that most of the principles described in this article can also be used in ASP.NET 1.x applications.

Many books about ASP.NET 2.0 (including my own), use Microsoft SQL Server 2005 Express Edition. The reason behind this choice is that the Express Edition is very easy to use and configure. In many cases, using a SQL Server Express database is as easy as adding a database to your project (or one of the new Login controls), and then hitting F5 to run the application. This makes the Express Edition great for local development and quick and dirty applications. However, in a production environment, the Express Edition won't cut it. Instead, you'll need one of its bigger brothers like SQL Server 2005 Standard Edition. But as soon as you try to make the move to one of these SQL Server versions, you may run into a number of security related configuration issues. This article takes a look at a number of common issues that you need to be aware of when you try to use SQL Server 2005 in your applications.

The article starts off with familiarizing you with the terminology that you'll run into when configuring the system. The second part then looks at a number of different scenarios for configuring an ASP.NET 2.0 application with SQL Server 2005. You'll see how to use a custom SQL Server database in your own application and how to configure both SQL Server and your application.

This article doesn't touch on configuring your database for the new ASP.NET 2.0 provider features, like Membership and the Role Manager. However, there's a great article by Scott Guthrie called "Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005" that shows you how to use tools like aspnet_regsql.exe to configure an existing SQL Server 2000 or 2005 to work nicely with the new provider features. That article, together with this one, should provide you with enough details to set up your system so it can work with a SQL Server 2000 or 2005 database.

Terminology and Concepts

When you want to connect to a SQL Server database, you have to authenticate yourself, so the database knows what user is trying to gain access. Microsoft SQL Server supports two different authentication mechanisms: SQL Server Authentication and Windows Authentication (often called Integrated Security).

SQL Server Authentication

With SQL Server Authentication, SQL Server takes care of user management. This means that users and their passwords are managed by SQL Server. You can access the user management functionality in SQL Server through the SQL Server Management Studio (for SQL Server 2005) or the Enterprise Manager (for SQL Server 2000). You'll see more on this later.

To connect to a SQL Server instance that uses SQL Server authentication, you need to pass a user name and password in the connection string of your application. A typical connection string looks like this:

	Data Source=YourServer;Initial Catalog=YourDatabase;
           User Id=UserName;Password=Password;

Windows Authentication

With Windows Authentication, the Windows OS takes care of user management. All interaction with the database is done in the context of the calling user (more on that later) so the database knows who's accessing the system, without an explicit user name and password being passed in the connection string. You still need to map a Windows account to a SQL Server account so SQL Server can determine whether the account has sufficient permissions. I'll show you how to do this later.

A typical connection string using Windows Authentication can look like this:

	Data Source=YourServer;Initial Catalog=YourDatabase;
             Integrated Security=SSPI;      

Instead of Integrated Security=SSPI you can also use Trusted_Connection=True which has the same effect.

Windows or SQL Server Authentication?

In general, it's recommended to use Windows authentication. The fact that you don't need to use a password in the connection string, means your application will be a bit safer. You don't need to send the password over the wire, and there's no need to store it in a configuration file for your application where it can be viewed by anyone with access to that file.

However, SQL Server Authentication is a bit easier to use. Since you specify your own user name and password, you don't need to know the final user account that your application runs under.

Notice that it is possible in SQL Server to disable SQL Server Authentication (but not Windows Authentication). You can configure this setting through the server's Properties dialog in the Management Studio or Enterprise Manager. The following screen shot shows the settings dialog for SQL Server 2005:

The SQL Server 2005 Server Properties Dialog

Figure 1: The SQL Server 2005 Server Properties Dialog

You can see from this screen shot that this instance of SQL Server is configured to allow both authentication mechanisms.

User Accounts

Another area you need to know a bit more about is user accounts. When you use SQL Server Authentication, things are simple. The account used is the account you specify in the connection string. But when you have a connection to SQL Server with Windows Authentication, that connection runs under the credentials of a specific user account. What that account is, depends on the application and its configuration. The following FAQ explains how you can find out the user account when you are using Internet Information Services (IIS) as the web server: "How Do I Determine the Security Account that IIS Uses to Run My Web Site?". For most scenarios this FAQ will be enough; however, when you are running a web site from within Visual Studio 2005 using the built-in development web server Cassini, the actual account used by the web server process is your own account that you use to log on to your Windows machine. So, keep that in mind when you have a site in Visual Web Developer and run it against the development web server.

Now that you are familiar with the important terminology, it's time to look at a number of different scenarios. In the section that follows, I'll show you how to configure your application and SQL Server in 3 different scenarios. For these scenarios, I'll use a machine called Development that holds the web site and an installation of SQL Server called Development\Sql2005. In my examples, I am using a database called BugBase, taken from my latest book ASP.NET 2.0 Instant Results.

Attaching Databases to SQL Server

Before I show you the three different scenarios, let's take a look at attaching databases to your SQL Server installation. Unlike file based databases like Microsoft Access, a SQL Server database need to be attached to a running instance of SQL Server. Once you've attached the database, it remains attached until you either delete or detach it. This works a little bit different for databases that run under SQL Server Express. For more details about this, check out Working with User Instances on the MSDN site.

  1. To attach a database, open up SQL Server Management Studio from the Microsoft SQL Server 2005 start menu item. After you logged in, you'll see the following screen:

    SQL Server Management Studio
    Figure 2 - Microsoft SQL Server Management Studio
     
  2. Right-click the Databases node and choose Attach. In the dialog that follows, click the Add button, browse for your .mdf file and click OK twice to attach the database.
     
  3. Once you have added the database, you may need to rename it. By default the database is named after the full path to the .mdf file. To rename the database, click it once, press F2 and type a new name. In my case, I renamed the database to BugBase.

At this stage, by default only the administrative accounts (Windows administrators or the SQL Server SA account) have access to the database. With your database attached to your instance of SQL Server, it's time to look at configuring the database and the application.

Scenario 1 - Using SQL Server Authentication

From a configuration point of view, this is probably the easiest scenario to configure: all you need to do is create a user in SQL Server, assign a password and then use that account in the connection string. The first action in this process is to add a user account. To do this, follow these steps:

  1. Open the Security node that you can see in Figure 2, then right-click on Logins and choose New Login.
     
  2. Select the SQL Server authentication option, and then type a user name and password. In my examples, I am using MyWebsiteUser as the username, and Pa$$w0rD as the password.
     
  3. Finally, click the OK button.

The final step is to give this new account the proper permissions to your database:

  1. Go back to the database you attached previously, expand the database, then expand Security, and finally expand Users. You should see something similar to this:

    Figure 3 - The Expanded View of the BugBase Database
    Figure 3 - The Expanded View of the BugBase Database
     
  2. Right-click the Users node and choose New User.
     
  3. In the Username text box, type MyWebsiteUser.
     
  4. In the Login name text box, type MyWebsiteUser. Alternatively, you can click the little button with the three ellipses and browse for the user name.

  5. At the bottom half of the screen, you see a box labeled Database role membership. In this box, you can choose a number of roles that you want to grant to your new user. The rule here is: give users as little permissions as possible. A good choice is db_datareader and db_datawriter that allows the account to both read from and write to tables in the database.


    Figure 4: The Database Role Membership List.

    Check out SQL Server's Books Online for more information about the various roles.
     
  6. There's a fair chance that only reading and writing to tables is not enough. For instance, you could have a number of stored procedures that the user account must be able to execute as well. To grant the account execute permissions on your procedures, click Securables in the left hand pane and click the Add button. In the dialog that follows, choose All objects of the types and then click OK. Then check Stored Procedures and click OK. The list with Securables will be filled with a number of stored procedures, including your own. Locate your stored procedure, and then make sure at least Execute is selected in the Grant column of the Explicit permissions box.

    The Permissions Dialog for a Stored Procedure
    Figure 5 - The Permissions Dialog for a Stored Procedure

    Repeat this step for every custom stored procedure in your database.
     
  7. Finally, when all permissions have been set, click the OK button to apply all the changes to the new user account.

Pfew, a lot of work, right? Fortunately, most of the work is now done. The next step is testing it out. In the following section, I'll walk you through creating a simple web site that displays data from the BugBase database with the user accounted created and configured in the previous steps.

  1. Start up Visual Web Developer or Visual Studio 2005 and create a new web site. It doesn't matter whether you choose C# or Visual Basic as the language for the site.
     
  2. Open the Web.Config file and add a connection string that looks like this:

    			<connectionStrings>
      <add name="BugBase" 
         connectionString="Data Source=.\SQL2005;
               Initial Catalog=BugBase;
               User ID=MyWebsiteUser;password=Pa$w0rD" 
         providerName="System.Data.SqlClient" />
    </connectionStrings>
    Don't forget to replace the items that are highlighted in this connection string.
     
  3. From here, you can use the connection string in ASPX pages. How you do this depends on your requirements, but you could for example use this connection string in a SqlDataSource control that is used as the data source for a GridView. The following code snippet shows a SqlDataSource and a GridView that displays two columns from the Bug table in the BugBase database:


    			<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
              ConnectionString="<%$ ConnectionStrings:BugBase %>" 
              SelectCommand="SELECT [Id], [Title] FROM [Bug]">
    </asp:SqlDataSource>
    
    <asp:GridView ID="GridView1" runat="server" 
              AutoGenerateColumns="True" DataKeyNames="Id" 
              DataSourceID="SqlDataSource1">
    </asp:GridView>
  4. Once you create a page that can display data, you can view it in your browser. If it all worked out as expected, you should see the data from the database being displayed. If you get an error, make sure that you used the right account and password in the connection string and that SQL Server is configured for SQL Server Authentication.

Scenario 2 - Using Windows Authentication with IIS and the Database on the Same Machine

This is a common scenario, especially when you're developing your site on your local machine. Both the web server (either IIS or the built-in development web server) and SQL Server run on the same physical machine. This scenario makes it easy to use Integrated Security because both the web server and SQL Server can use the same Windows account. To configure your server for this scenario, follow these steps:

  1. Start by determining the account used by your web server. Check out this FAQ for more details: "How Do I Determine the Security Account that IIS Uses to Run My Web Site?". For the remainder of this section, I'll use the ASPNET account on my machine called Development.
     
  2. Next, you need to map this Windows account to a SQL Server account. To do this, open up Microsoft SQL Server Management Studio and log in to your SQL Server instance. Expand the Security node (that you can see in Figure 2) and then right-click Logins and choose New Login. Type MachineName\ASPNET in the Login name box, where MachineName is the name of your Windows machine, Development in my case. Make sure that Windows authentication is selected:

    The New Login Dialog in SQL Server Management Studio
    Figure 6 - Part of the New Login Dialog
     
  3. With the Windows account mapped to a SQL Server login the next step is to assign this login the appropriate permissions in your database. To do this, expand the Databases node (visible in Figure 2) and then expand Security. Right-click the Users node and choose New User. In the Username box, type ASPNET and in the Login name type MachineName\ASPNET. Alternatively, click the button with the ellipses, then Browse to select the account from the Matching objects list. You should end up with something like this:

    The New User Dialog
    Figure 7 - The New User Dialog
     
  4. At the bottom half of the screen, you see a box labeled Database role membership. In this box, you can choose a number of roles that you want to grant to your new user. For this example, choose db_datareader and db_datawriter. For more details about configuring roles and permissions for other other objects like stored procedures, see steps 5 and 6 of scenario 1 earlier in this article.
     
  5. Finally, when all permissions have been set, click the OK button to apply all the changes to the new user account.

The next step is testing out these settings. In the following section, I'll walk you through creating a simple web site that displays data from the BugBase database with the user accounted created and configured in the previous steps. Notice that these steps are similar to those from scenario 1 earlier in this article. However, you'll need to use a different connection stirng, and configure IIS for your web site.

  1. Start up Visual Web Developer or Visual Studio 2005 and create a new web site. It doesn't matter whether you choose C# or Visual Basic as the language for the site. Save the site in a folder like C:\TestWebsite.
     
  2. Open the Web.Config file and add a connection string that looks like this:

    			<connectionStrings>
      <add name="BugBase" 
        connectionString="Data Source=.\SQL2005;Initial Catalog=BugBase;
        Integrated Security=SSPI;" providerName="System.Data.SqlClient" />
    </connectionStrings>
    Don't forget to replace the items that are highlighted in this connection string to values that apply to your situation.
     
  3. From here, you can use the connection string in ASPX pages. How you do this depends on your requirements, but you could for example use this connection string in a SqlDataSource control that is used as the data source for a GridView. The following code snippet shows a SqlDataSource and a GridView that displays two columns from the Bug table in the BugBase database:


    			<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
              ConnectionString="<%$ ConnectionStrings:BugBase %>" 
              SelectCommand="SELECT [Id], [Title] FROM [Bug]">
    </asp:SqlDataSource>
    
    <asp:GridView ID="GridView1" runat="server" 
              AutoGenerateColumns="True" DataKeyNames="Id" 
              DataSourceID="SqlDataSource1">
    </asp:GridView>
  4. Once you have created the page that can display data, you should configure IIS so it points to your web site's root folder. To do this, open the Internet Information Services Manager and locate your web site (most likely, this site is called Default Web Site). Right-click it and choose Properties. On the Home Directory tab, enter C:\TestWebsite (or the location where you created the web site in step 1) in the Local path box. Next, switch to the ASP.NET tab and make sure that the site runs against version 2.0 of the framework.
  5. Finally, browse to http://localhost in your web browser. If all worked out as expected, you should see the data from the database being displayed. If you get an error, make sure that you used the right account and password in the connection string and that SQL Server is configured for SQL Server Authentication.

Scenario 3 - Using Windows Authentication with IIS and the Database on different Machines

This is another common scenario and one that you're likely to encounter in a production environment. The web site (or sites) run on a dedicated web server, while the data comes from a dedicated SQL Server machine. Quite often, the authentication mechanism in this scenario is SQL Server authentication. The reason behind this is that it's easy to set up. You don't need to be aware of what account is used by the web server, as you explicitly send a user name and a password in the connection string to the database. However, you don't have to use this security mechanism; it's quite possible to use Windows Authentication as well as long as the two machines are on the same network.

When using this scenario, there are two options that you need to be aware of: both machines fall under the same Windows domain, or both machines can be seen as independent, stand alone machines. In both configurations, the actions you need to perform are very similar to those of scenario 2. However, it's the exact user account that is different. The next two sections describe this in more detail.

Sharing the Same Windows Domain

When both machines are part of the same Windows Domain, you can configure IIS to run the web site under a specific domain account, like YourDomain\AnonIIS. For more details about configuring IIS, check out this FAQ: "How Do I Setup IIS to Allow it to Use Network Resources?" There's one caveat that you need to be aware of in ASP.NET pages: even if you specify a domain account in IIS, the ASP.NET web site will still run under MachineName\ASPNET or MachineName\Network Service.

However, you can change this behavior by modifying the Web.Config file for the application. To make the change, you can add an <identity impersonate="true" /> element to the <system.web> section. If you add the element, IIS will impersonate the current user and use that account instead of the ASPNET account. In this case, the "current user" means the account you set in the Anonymous access box when you're using anonymous access. Otherwise, the current user means an authenticated visitor.

When you have configured IIS to run under a domain account, and configured the application to use impersonation, the final step is to add the domain account as a user to SQL Server and your database. This works almost the same as described in scenario 2. The only difference is that the account you need to add follows the pattern of Domain\AccountName and not MachineName\AccountName.

Two Stand Alone Machines

Once again, this scenario is similar to scenario 2 in terms of configuring SQL Server. However, since both machines have different names, and thus different user accounts, it may seem impossible to add the account that is used by IIS on one machine to SQL Server on the other. The trick in this scenario is to use "mirrored accounts". You create accounts with identical names and passwords on both machines, for example MyWebsiteAccount. On the machine that runs IIS, you use this account in the Anonymous Access dialog for IIS and add an <identity impersonate="true" /> element to your application's configuration file. For more details, see the previous section "Sharing the Same Windows Domain". Then on the database server, you add the account MachineName\MyWebsiteAccount to SQL Server and your database. This works at run time, because the internal representation of the Windows account will be the same, regardless of the machine where the account was created.

For more information about mirroring the accounts, check out the section "Synchronize the IUSR Account" of this FAQ. Although the FAQ speaks about synchronizing the IUSR account in a classic ASP scenario, the same principles apply to ASP.NET.

Other Issues

When working with SQL Server 2005, you may also receive the following error: "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."

In a default installation, SQL Server only allows local applications to connect. To resolve this, and grant remote systems access to the database as well, follow these steps:

  1. Open the SQL Server Configuration Manager from the Microsoft SQL Server 2005 start menu item. Depending on the version of SQL Server you're using, this item may be located under the Configuration Tools sub menu.
  2. In the window that appears, locate your instance of SQL Server under the SQL Server 2005 Network Configuration item and click it.
  3. In the list with protocols on the right, right-click Named Pipes and choose Enable if its status is currently set to Disabled.
  4. Repeat the previous step but this time configure TCIP/IP.

You'll need to restart SQL Server for the changes to take effect. You can do this under the SQL Server 2005 Services node in the same dialog, or through the Services panel of the Administrative Tools of Windows.

Summary

As you have seen, securing SQL Server and ASP.NET applications is a daunting task. In many situations, you're better off leaving this to your system or database administrator. However, if you don't have a security specialist, or you are the system or database administrator, this article hopefully has given you some valuable insight in securing SQL Server and your application.

Please note that since this security topic is so broad, I haven't been able to describe each and every scenario, step or concept in great detail. If you feel something important is missing, feel free to contact me, describe what you're missing and I'll update the article.

Download Files

Source Code for this Article
This download contains a default ASP.NET page with a simple GridView and a SqlDataSource so you can test things out. It also contains a sample Web.Config file with two connection strings: one that uses SQL Server Authentication and one that uses Windows Authentication. Don't forget to update both the connection strings and the SELECT statement in the ASPX page when you use this sample. Also, since the Web.Config file contains two connection strings with the same name, you need to make sure that you comment out the one you don't need.

References


Where to Next?

Wonder where to go next? You can read existing comments below or you can post a comment yourself on this article .


Consider making a donation
Please consider making a donation using PayPal. Your donation helps me to pay the bills so I can keep running Imar.Spaanjaars.Com, providing fresh content as often as possible.



Feedback by Other Visitors of Imar.Spaanjaars.Com

On Wednesday, July 26, 2006 3:02:56 PM Jessica said:
Could you post an sample of Configuring an ASP 3.0 Application to Work with Microsoft SQL Server 2005? Thanks.
On Thursday, August 17, 2006 6:56:18 PM Andy said:
A really basic and exellent guide!

Thanks!
On Thursday, September 07, 2006 8:29:30 AM Payal said:
Very well written article! The look and feel of the site also complements the presentation of the article. Hats off to you and your team!
On Friday, September 15, 2006 2:17:18 PM Imar Spaanjaars said:
Hi Andy and Payal,

Thank you for your kind words.... Glad you like the article.

Cheers,

Imar

On Sunday, September 17, 2006 6:22:44 PM angie said:
your article helped me greatly! with no real dba to work with, this was a great resource. thanks.
On Tuesday, October 10, 2006 3:49:56 PM Matt Barton said:
I spent 4 hours pulling my hair out struggling with SQL Server 2005 last night, and you've saved me another night of the same with this guide.

THanks very much.
On Monday, December 04, 2006 4:51:53 AM sara said:
heartfelt thanks for the article
On Thursday, December 07, 2006 12:18:47 AM Riz said:
Hey Imar,

Really excellent article, and timely. I've already spent two nights on this. I'm in a situation where I've got the web server in a DMZ separated by a firewall to the DB server in the internal LAN. The DMZ and LAN have their own Active Directory. In the DMZ, I've created an AD account for my web app, and configured the app to use this account to authenticate to the database. I've opened up ports in the firewall that allows me to ping the DB server, and I currently get an error notification from the DB server, so I know that the database is responding.

I am thinking of creating an account on the SQL Server machine as Domain\AccountName where "Domain" is the DMZ domain name, and then adding this account to a group, and then configuring SQL Server Login to use this group.

Would you say that I'm on the right track?

Thanks.
On Thursday, December 07, 2006 11:34:12 AM Imar Spaanjaars said:
Hi Riz,

Sounds like you're on the right track. However, if both environments have their own AD, I think you need to create a "trust" between them. Without a trust one domain won't trust the other so authentication will fail.

Search the MSDN site for "create trust" for more info...

Cheers,

Imar
On Friday, December 08, 2006 9:10:16 AM Riz said:
Hi Imar,

Thanks for the quick reply! I read about creating trusts between domains, and then I cam across another solution on MSDN at http://msdn2.microsoft.com/en-us/library/ms998292.aspx

I created a mirrored user account locally on the DB server on the internal LAN (same UID/PWD as my service account on the web server), added this user to a group, created a SQL Server login for the group, and set it up to use Windows authentication. I then followed your advice to give the user some basic rights, including the ability to execute stored procs.

Mirrored accounts worked for me, but MSDN acticle suggests that this is not recommended. Can you comment on that?

Thanks a bunch!
Riz
On Friday, December 08, 2006 11:14:35 AM Imar Spaanjaars said:
Hi Riz,

I think I have to agree with Microsoft here. The problem with mirrored accounts is that they are quite vulnerable. Since you have two identical accounts in two different environments, it's possible they get out of sync. For example, someone changes the password on one server, but not on the other.

With a trust, you don't run into this as you add an account from one environment to another, and not create a copy of it....

Imar
On Tuesday, January 09, 2007 9:58:06 AM Taki said:
Useful article!

Greate work!
On Monday, February 05, 2007 11:59:51 AM Manish Kulkarni said:
Nice article Imran. Thnx. It  helped me very much in ASP.NET 2.0 + SQL SERVER 2000 programming.
On Monday, February 05, 2007 12:05:14 PM Imar Spaanjaars said:
Hi Manish,

Imran??? Who's that?

Imar
On Sunday, February 11, 2007 1:59:41 AM Roger said:
I keep getting this error.  I'm using VS 2005 std edition.

Can you help please?  

Server Error in '/WroxUnited' Application.
--------------------------------------------------------------------------------

The user instance login flag is not supported on this version of SQL Server. The connection will be closed.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The user instance login flag is not supported on this version of SQL Server. The connection will be closed.

Source Error:


Line 109:    public virtual string Theme {
Line 110:        get {
Line 111:            return ((string)(this.GetPropertyValue("Theme")));
Line 112:        }
Line 113:        set {


Source File: c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\wroxunited\fa
On Sunday, February 11, 2007 9:57:21 AM Imar Spaanjaars said:
Hi Roger,

Did you try removing the User Instance attribute from the connection string? Since the error message says it's not supported on your version of SQL Server, that would be the first thing I'd look at.

Imar
On Tuesday, February 20, 2007 3:41:44 PM Terry Bottorff said:
I think this question applies to this article. I get the following error:

Unable to open physical file "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\myfile.mdf". Operating system error 32: 32(The
process cannot access the file because it is being used by another process.)"
An attempt to attach an auto-named database for file "C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\myfile.mdf" failed. A database
with  the same name exists, or specified file cannot be opened, or it is
located on UNC share.

Do you have any ideas????? TIA.
On Tuesday, February 20, 2007 5:49:00 PM Imar Spaanjaars said:
Hi Terry,

How do you connect to SQL Server? How does your connection string look like? What version of SQL Server are you using? When and how are you getting this exception???

Lots of questions, so I think you're better off posting this on a forum like http://p2p/wrox.com

Cheers,

Imar
On Wednesday, February 21, 2007 6:31:17 AM jake williamson said:
i cant thank you enough for this article, it's really helped me get sql server express working with both visual studio express web edition and dreamweaver.

i used 'Scenario 1 - Using SQL Server Authentication' and it worked a treat!

one question though; section 6 goes through setting up permissions on stored procedures. do you have to do this every time a stored procedure is created? it would be great if the user automatically got permissions to use any new sp's as they are created?

fantastic work and thank you again for posting this up,

jake
On Wednesday, February 21, 2007 6:44:04 PM Imar Spaanjaars said:
Hi jake,

yes, unfortunatley, AFAIK, you should do this every time for an account that doesn't have these permissions (e.g. not DBO / SA etc).

You may be able to create a role for it in SQL 2005, but then still for each SP you would need to apply it to the SP...

Imar
On Monday, March 19, 2007 1:59:52 PM emmy said:
hi there,please help me
when i attached the db throught sql server management studio i got this error
Failed to to retrieve data for this request(Microsoft.SqlExpress.SmoEnum)
An exception occured while executing a transact-sql statement or batch
Create file encountered operating system error 32(error not found)
while attempting to open or create the physical file D:\XCopyDeploy\App_Data\company.mdf

also i have this problem
Unable to open the physical file "D:\XCopyDeploy\App_Data\compa

<snip>Rest of the code cut by imar</ snip>
On Monday, March 19, 2007 2:10:36 PM Imar Spaanjaars said:
Hi emmy,

Please try a technical forum like http://p2p.wrox.com for questions like this.

Cheers,

Imar
On Wednesday, March 28, 2007 12:51:39 PM Gabriele said:
Hello,
everybody here is talking as SQL Server 2000 or 2005 are identical or behave in the same manner, but it's not... I'm not able to find any way to connect an ASP.NET 2.0 application to a SQL Server 2000. It only works if I run it from my VWD Express, in every other way (local IIS or IIS on the same machine at the database) I'm gettin the error saying that remote connections are not enabled in SQL Server 2005.

But what the hell!!! I DO NOT HAVE 2005, I HAVE 2000!!! How can I solve it???

If someone has some ideas that is NOT ABOUT 2005, please write me here.
On Wednesday, March 28, 2007 1:54:08 PM Imar Spaanjaars said:
Hi Gabriele,

No need to shout; we hear you loud and clear.

The error message you get just says that *if* you're using SQL 2005, it doesn't accept remote connections by default. It doesn't mean you're using SQL 2005; it's just a message from the database povider trying to be helpful.

Did you follow the steps from this article? Did you read the "How Do I Determine the Security Account..." FAQ I linked to? What account did you add and configure in your SQL Server installation?

Imar
On Wednesday, March 28, 2007 2:11:21 PM Gabriele said:
Hi... sorry for shouting, I really did not want to be unpolite... :-(
But my head is going to explode... :-D

The step in this article do not apply to SQL Server 2000. It has no SQL Management Studio, just the Enterprise Manager and the screens shown here are not present or however different. Further, there are no schemas or roles beggining with "aspnet_".

I have to specify that my case is Scenario 2.
Why temporary ASP.NET 2.0 development server (the one that appears in the tray icon) can log and run into SQL Server 2000 and everything else doesn't??

Even the aspnet_regsql.exe command stops with the same error...

Help!!!
On Wednesday, March 28, 2007 2:16:22 PM Imar Spaanjaars said:
Hi Gabriele,

You indeed need to use the Enterprise Manager. It also has a Users node in the tree structure (under your server, at the same level as Databases).

You need to add and configure an account there. What that account is depends on your system. Read the FAQ to find that out. You can make the account a DataReader and DataWriter and grant it permissions to your stored procedures.

The FAQ also explains why VWD works, and IIS doesn't. The built-in web server in VS 2005 runs under *your* credentials, while IIS runs under an account called Network Service or ASPNET.

Imar
On Tuesday, April 10, 2007 10:46:22 PM Rae barton said:
Great article. I have one question, though.

How can I customize actual login form? I have login form and registration form that ask users for more information than the default template Sharepoint uses. I found a few articles that demonstrates how to switch from Winodws authentication to Form authentication but haven't found any article about how to customize the Login form or registration form. Any reference would be great.
On Wednesday, April 11, 2007 5:11:43 AM Imar Spaanjaars said:
Hi Rae,

Take a look here: http://weblogs.asp.net/scottgu/archive/2005/10/18/427754.aspx

Imar
On Wednesday, April 18, 2007 8:02:22 AM Ashok ku,ar said:
sir
i am web developer and facing some problem with Asp.Net 2005 that when designed application made in Dreamweaver is taken the I cannot understand that how i write code and connect with database .
plz give me tips with example.
On Wednesday, April 18, 2007 9:16:59 AM Imar Spaanjaars said:
Hi Ashok,

Take a look here:

http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=423

Imar
On Tuesday, April 24, 2007 1:31:33 AM Rodney Santiago said:
Do you have any examples using Sql Server 2005 Application Role and C# Project?

BTW, Thanks for the article. Great Info.

Thanks,
Rodchar
On Tuesday, April 24, 2007 5:34:02 AM Imar Spaanjaars said:
Hi Rodney,

No, I don't have any examples, but I am sure Google does...

Imar
On Thursday, May 24, 2007 6:37:49 PM Peter said:
Hi Imar,

I am trying to use the approach described in scenario 1 for connecting to a local SQLServer 2005 database from a simple C# console application using the ADO.NET SqlConnection object and a connection string that looks like: SqlConnection conn = new SqlConnection("data source=.\\SQLEXPRESS; initial catalog=TestDB; user ID=MyDBUser; password=p$$w0rd");

My call to conn.Open() generates the exception "Cannot open database \"TestDB\" requested by the login. The login failed. \r\nLogin failed for user 'MyDBUser'."

It seems that my SQLServer instance recognises the user and password (because I get a different error if the password is invalid) but something is preventing my user from opening the  database.

I am pretty sure that I have followed your instructions to the letter. Do you have any suggestions please?

Thanks for putting together such a well-written article.

Rgds,

Peter
On Friday, May 25, 2007 11:32:09 PM Imar Spaanjaars said:
Hi Peter,

Can you log in from the SQL Server Management Studio with these credentials?

Imar
On Saturday, May 26, 2007 2:39:47 PM Peter said:
Hi Imar,

Yes, I can log in and query the database with SQLServer Mgmt Studio or Visual Studio. And I can also use Windows or SQLServer Authentication to log in.

So far, the only way I have been able to use the  SQLConnection object from my C# application is to query tables created in the System Databases/Master/Tables area of my default SQLServer instance.

Rgds,

Peter
On Saturday, May 26, 2007 2:45:12 PM Imar Spaanjaars said:
Hi Peter,

Then maybe you didn't give the account MyDBUser the necessary permissions (like DataReader / DataWriter and so on) in the database TestDB?

Otherwise, I don't know what's going on.

Imar
On Tuesday, June 12, 2007 4:15:57 AM Doug Schloer said:
This is a very good article that I found well written and informative - Actually, I was having a login error issue when connecting to an SQLEXPRESS instance from an ASP.NET 2.0 web app. I must have burned 5 hours trying to resolve the error. I found the info I needed in 5 minutes of reading in the first paragraph - Awesome!! Thanks!!
On Friday, June 29, 2007 12:30:15 AM Mark said:
I can't thank you enough for this well-written guide. Coupled with your article
"How Do I Determine the Security Account that IIS Uses to Run My Web Site?", you have saved me hours of work and have given me a better understanding of how the authentication process works.
On Friday, June 29, 2007 5:28:20 PM Imar Spaanjaars said:
Hi Mark,

You're more than welcome; glad you like it.

Cheers,

Imar
On Thursday, July 05, 2007 6:34:33 PM Vasudeo said:
I was searching for these for almost 2 hours. Finally my connectin worked.
Thanks Imar.
On Sunday, July 15, 2007 12:36:56 AM Justin said:
I have been having trouble getting windows authentication mode to work - I've managed to get SQL auth. to work but would prefer windows auth.  I have followed all of the steps in your guide including mapping the windows account to an SQl server and assigning roles/permissions to that account.  I have IIS and SQL on the same machine, and also I'm using SQL Express.   Currently I'm just trying to get this to work with ASP classic...

this is my connection string:  
strConn = "Data Source=.\SQLEXPRESS;Initial Catalog=fallendb;Integrated Security=SSPI;"

I get the following error:  
Microsoft OLE DB Service Components (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/gb_game/city_view.asp, line 64

I have been searching for hours and yours is the best guide I've found.  Its greatly appreciated.  Basically I think I just need a valid connection string, and if the provider needs to be set how would I do that in ASP classic?  Thanks a bunch for all your hard work!
On Sunday, July 15, 2007 8:40:13 AM Imar Spaanjaars said:
Hi Justin,

Take a look here:

http://www.connectionstrings.com/

Look under SQL 2000 or SQL 2005 and then look for Ole DB connection strings.

Cheers,

Imar
On Monday, July 16, 2007 12:10:37 AM Justin Hays said:
I still don't know why it isn't working.  I've perused forum after forum and googled many a time.  I've tried lots of connection strings but I can't get it to go with windows auth.   I'd prefer not to use OLEDB since part of what I'm doing is to teach myself the new technology.  

This conn string works fine for SQL auth:

strConn = "Driver={SQL Native Client};Server=BABYLON777\SQLEXPRESS;Database=fallendb;Uid=iguana777;Pwd=xxxx;"

But the following strings do not work for Windows Auth:

strConn = "Provider=sqloledb;Data Source=BABYLON777\SQLEXPRESS;Initial Catalog=fallendb;Integrated Security=SSPI;"

strConn = "Driver={SQL Native Client};Data Source=BABYLON777\SQLEXPRESS;Initial Catalog=fallendb;Integrated Security=SSPI;"

I'm concerned that it is a configuration issue is SQL or who knows what.  Sorry this may be a little off topic but after reading your article I do feel like if anyone will have a good answer for me it will be you!  Thanks a bunch
On Monday, July 16, 2007 12:15:55 AM justin hays said:
BTW with the OLEDB conn string from above (from connectionstring.com) the error is:
Microsoft OLE DB Provider for SQL Server (0x80004005)
Cannot open database "fallendb" requested by the login. The login failed.
On Monday, July 16, 2007 5:35:38 AM Imar Spaanjaars said:
Hi justin,

Now that is a useful error message. It means that the account the web server is running under doesn't have permission to access the database.

Take another look at the section User Accounts of this article. What account is your web server running under? Are you sure that is really the case? And did you create a SQL login for that account?

Imar
On Monday, July 16, 2007 9:32:42 PM Justin Hays said:
AHA!  Ok here is what I was doing - for some reason I thought that in your example ASPNET (on your machine DEVELOPMENT) was a key word.  So instead of creating my own MYDOMAIN/IUSR_MYMACHINE user account I was creating MYDOMAIN/ASPNET - which didn't do squat.  Finally (four days later) it works now - thanks so much for your help and your amazingly well written article.  Kudos deluxe turtle supreme style to Imar!  Thanks again
On Monday, July 16, 2007 10:20:12 PM Gina Cresse said:
Hi.  Thanks for the great article.  It seems so easy and straight forward, but try as I may, I still get the following error:

"An error has occurred whil establishing a connectio nto the server.  When connecting to SQL Server 2005, this failure may be cause by the fact that under the default settings SQL Server does not allow remote connections.  (provider: SQL Network Interfaces, error: 25 - Connectio nstring is not valid)"

I'm trying to add a database connection to a GridView in my IIS website created with Visual Studio 2005.  The website and SQL Server 2005 are all on the same machine.  I followed your instructions to determine the account being used, and although the Authentication Methods window does have a checkmark in the Anonymous access box, and the User name of IUSR_GINA, I was fairly sure the actual user is ASPNET because before I hit this brick wall, I'd hit another one trying to get the Membership functionality to work and Scott Guthrie suggested I needed to grant permissions to ASPNET, which I did and then Membership login worked.  This is the same website.

Anyhow, I don't know what else to try.  Any suggestions you have will be greatly appreciated.

Thanks,
Gina
On Monday, July 16, 2007 10:32:22 PM Gina Cresse said:
I forgot to include the connection string that gets generated when I try to create the database connection in the previous post:

Data Source=(LOCAL)\MSSQLSERVER;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf";Integrated Security=True

Thanks,
Gina
On Tuesday, July 17, 2007 5:58:39 AM Imar Spaanjaars said:
@Justin:

Glad it's all working now.

@Gina:

Yes, for ASP.NET, the account is most likely indeed ASPNET (or Network Service). The FAQ I linked to describes this in more detail. Maybe you started with the classic ASP section?

Are you sure the server is called (LOCAL)\MSSQLSERVER and not (LOCAL)\SqlExpress?? Secondly, I don't think you need the double quotes around the .MDF file name

Finally, depending on your OS and configuration, your account may not have the proper permissions to read to the MDF file. Try moving it to a different folder like D:\Data and try again.

Cheers,

Imar


On Wednesday, July 18, 2007 2:45:11 PM Byron Rode said:
This was the easiest and most detailed tutorial I could find.

I am a PHP developer and have never used either ASP.NET or MSSQL up until today. I was really confused, until I came across this.

This made it easy. Thanks!
On Thursday, September 13, 2007 1:22:02 PM kamal soni said:
your material is very nice . please you send me good  and simple understanding material for dridview , dataset, details view , treeview
.

Thankyou.  


kamal soni.
On Thursday, September 13, 2007 2:35:31 PM Imar Spaanjaars said:
Hi kamal,

Try the ASP.NET quick starts at: http://quickstarts.asp.net

Imar
On Thursday, September 20, 2007 11:04:41 AM Soujanya said:
Thanks a lot.
This article worked.
On Monday, October 15, 2007 3:00:05 PM Bjorn said:
Thank you!
Your scenario 1 works. But it still sucks! (Not that it's your fault :-)).
I want web developers, using VS.NET 2005 Professional, to be able to create their own databases and use them without me attaching them to the SQL Server 2005 (Enterprise Edition) running on the web server. I also hate that developing local and remote differs!
Thank you again!
On Friday, October 26, 2007 10:29:23 PM vaf said:
I have an ASP application which is working well with SQL 2000 but I can not run it with SQL EXpress 2005.
both IIS and database are sitting on the same server (windows 2003).

I get this error message: SQL SErver Does not exist or access denied
Any idea?
Thanks

On Saturday, October 27, 2007 9:00:19 AM Imar Spaanjaars said:
Hi vaf,

Are you sure you are correctly addressing the Express Edition through its instance name? E.g. (local)\Express for example?

If that doesn't help, please post your question on a forum like http://p2p.wrox.com and provide lots more detail about your setup.

Cheers,

Imar
On Saturday, December 01, 2007 9:38:47 PM Mohammadreza said:
Hi Imar,
Thank you very much for your useful article. It saved me from wasting
too much time.
Good luck man.
On Monday, February 04, 2008 10:36:44 PM Carl said:
so i've written a vb app (.net 2.0) that gets / sets data from sql server 2005.  it works perfectly fine on my development machine.  I put in in wwwroot on my server, and it works perfectly fine there too (by going to "localhost\" in IE on the server).   If i access the server from another cpu within my network, no problems there either (eg:  "192.168.10.10\MyWebApp.aspx" in IE on another computer).  However - when i access the page from an external computer via the internet, i have problems.  The page is there, but data from SQL is not.  Does the aspnet user need to be in SQL server 2005 users or something?  What am i missing?
On Tuesday, February 05, 2008 5:57:31 PM Imar Spaanjaars said:
Hi Carl,

Maybe you run under your network credentials when logged on to the Intranet? (It's possible this happens automatically). When you access your site over the internet, you don't log on automatically. In that case, the ASPNET account is probably used. Refer to this article to find out what account to configure exactly.

Cheers,

Imar
On Thursday, April 03, 2008 6:41:41 PM Bob said:
Great article!

Bob
http://www.lessonplansforfree.com
On Friday, April 18, 2008 6:50:48 AM Daniel Silva said:
Hi Imar.
I read your atrticle with interest, is very good. I read it cause I thought find the solution of a problem that I still have.
I'm in the second scenario you mention in your article, I did all that you say there, but I continue with the problem: I can't attach a database to ASP.NET WebSite Project in VS 2005. I did my database in SQLServer2005. When I try to stablish the connection, I get this error: "The user instance login flag is not supported on this version of SQL Server. The connection will be closed. "
If you can help me, it will be great.
Thanks a lot.
Regards
D............................
On Saturday, April 19, 2008 1:54:08 PM Imar Spaanjaars said:
Hi Daniel,

You seem to be mixing concepts. You either attach a database to SQL Server and don't use the user instance attribute on the connection string, or do you use the attribute, but then you can't atach the database before you use it and instead need to rely on the auto attach features of SQL Server.

I think in your case, all you need to do is remove the offending attribute from the connection string.

Cheers,

Imar
On Monday, May 19, 2008 11:04:56 AM Mukesh Kr. Kundlia said:
Thanks a lot Imar...........

Your article help me a lot to solving my problem. I developed one site on vs.net 2.0 & Sql server express edition 2005. I am using login control to authenticate user on site. It was working fine on local server but When I published the site on IIS. It didn't authenticate the user and throwing msg "Login attempt fail, Please try again". I searched the google & got to know that it causing due to sql server authentication.
well my problem has been resolved but I can't say I got the whole article.
As I am novice to this pls could u tell me the basics of this.

Thanks again Imar....
Mukesh Kr. Kundlia
On Monday, May 19, 2008 5:21:28 PM Imar Spaanjaars said:
Hi Mukesh,

You may want to get yourself a book on SQL Server like Beginning SQL Server 2005 from Wrox.

Cheers,

Imar
On Wednesday, June 11, 2008 11:20:41 AM dr ahmed galal said:
for the first time i really understand this sup thank you very much more than great unswer i will try it and postback the result thanks again
On Sunday, August 03, 2008 9:12:48 AM Luis Rocha said:
Many thanks!

this post is awesome!! It helped me a lot.
On Friday, September 05, 2008 2:57:30 AM Lidya Agustina said:
This post is great!! I really helped me!
On Saturday, October 04, 2008 11:43:34 AM Roy Venema said:
Hi,

I have created a website that uses forms authentication. I also created a SQL login account (eg. webuser) for the website to connect to a SQL 2005 Express database.

In your article you state to give this user as less privileges as possible.
However my website containts an admin section that is used to set roles for users and/or lock out users. The admin needs permission to execute stored procedures (eg. aspnet_users_createuser). A basic user should not have this permission. I could grant the webuser premission to execute the stored procedure mentioned. But is this safe?

How do I solve this problem?  

On Saturday, October 04, 2008 11:57:52 AM Imar Spaanjaars said:
Hi Roy,

Yes, that's typically how you do it: you grant execute pemisisons to the SQL account used by the web server. Then your application should ensure that basic users cannot acces pages that perform actions you don't want them to carry out.

Imar
On Wednesday, October 08, 2008 11:05:30 AM Mohd Haq said:
Thanks for this well illustrated and detailled article of yours.
On Wednesday, October 08, 2008 11:30:19 AM Roy Venema said:
Hello Imar,

Thanks for you answer. I still got 1 question though. The admin user needs more privileges than a basic user. However there is only 1 general  application user. As a result the application user needs the same rights as the admin user, right? Isn't this a security risk?  
On Wednesday, October 08, 2008 12:10:14 PM Imar Spaanjaars said:
Hi Roy,

It all depends.

Quite often in a web application, the account used by the web server has the combined permissions for all users. Your code / site then needs to make sure only authorized users can access certain functionality. You can use ASP.NET URL authorization, CAS (Code Access Security), or your own code (among many other things) to block access. For example:

btnDelete.Visible = myUser.IsInRole("Administrators")

This would hide the delete button for users who are not administrators.

URL authorization and CAS can take this even further, resulting in a secure and manageable solution.

Cheers,

Imar
On Friday, October 10, 2008 4:19:00 PM Al said:
Awesome article. Thanks for the help!!
On Wednesday, October 15, 2008 4:49:39 PM Nick said:
Thanks for writing this article...I think it is exactly what I need.  I followed the instructions for option 2, however I'm still receiving the following error:

Unhandled Exception: System.Data.OleDb.OleDbException: Login failed for user 'domain\my user name'.
Cannot open database "database" requested by the login. The login failed.

I am running Windows Server 2003 on a standalone machine (connected to a network) with SQL Server 2005.  For some reason, it seems like my app is trying to run using whatever account I am currently logged in to (I've tried with both domain and local accounts and received the same error) and not the ASPNET/Network Service accounts you mentioned.

I am very new to this realm of knowledge so I apologize if I misunderstood something simple in your articles that would have prevented my question.  Any input you could provide would be greatly appreciated.
On Wednesday, October 15, 2008 9:11:39 PM Imar Spaanjaars said:
Hi Nick,

Can you define "my app"? Is this an ASP.NET site running from within Visual Web Developer and the built-in web server?

If so, this is by design. You'll need to add your own account as well in that case...

Cheers,

Imar
On Thursday, October 16, 2008 12:14:20 PM Nick said:
Imar,

Thanks for the response.  I've created a small C# console application for my own learning purposes that I am compiling into an executable and running through the windows command line...not through Visual Studio.  All it does right now is try to open a connection to the database and then close the connection. Eventually I would like to use this same code in a web app or web part for use in SharePoint.

I don't know if it would be helpful at all, but the connection string I am currently trying is "Provider=SQLNCLI;Server=localhost;Database=test_database;Trusted_Connection=yes;"

Thanks again for your time!
On Thursday, October 16, 2008 7:59:48 PM Imar Spaanjaars said:
Hi Nick,

And the question is? Your console application runs under your credentials, so why don't you add that account to SQL Server?

Imar
On Thursday, October 23, 2008 10:36:05 AM frankie said:
Great article. Thanks to you I solved my problem.
On Thursday, April 16, 2009 9:50:56 AM Francis said:
I had problems related with:
1-) iis configure application
2-)System.Data.SqlClient.SqlException: Login failed for user domain\machine
I solved them before reading this article and I confirm all it's - very written - content.
On Saturday, April 25, 2009 10:07:06 AM Helio said:
This sql statement will grant a user execute permissions on all stored procedures including new ones. Assume 'TestUsr' is the login. Applies to SQL server 2005 and later:

GRANT EXECUTE ON SCHEMA::dbo to TestUsr
On Wednesday, June 24, 2009 1:26:28 PM Ayyapparajan said:
Hi All,

I am beginner in VS2008 Web application (VB), so can you tell me any online materials available or followup books both Beginners and Experts.

Thanks
Ayyappan
On Wednesday, June 24, 2009 5:41:58 PM Imar Spaanjaars said:
Hi Ayyapparajan,

Take a look at my book Beginning ASP.NET 3.5:

http://imar.spaanjaars.com/AboutMyBooks.aspx?aboutitem=beginningaspnet35

Cheers,

Imar
On Monday, June 29, 2009 4:56:27 PM Puneet Singhal said:
I have studied your article it was very nice, but as I am using SQL express & windows authontication, how do I get the interface for setting all this permission on clint machine.

please help me out, as I am stuck with problem from last 2 week.
On Monday, June 29, 2009 5:15:35 PM Imar Spaanjaars said:
Hi Puneet,

You can download SQL Server2008 Management Studio Express for free from the Microsoft site:

http://www.microsoft.com/downloadS/details.aspx?familyid=08E52AC2-1D62-45F6-9A4A-4B76A8564A2B&displaylang=en

Cheers,
Imar
On Tuesday, June 30, 2009 11:27:24 AM Puneet Singhal said:
Hi Imar,
Thanks for your quick reply !

Actually we have created an outlook 2007 add-in, that is added to an users outlook, which uses sqlexpress as DB and use windows authentication for DB access. it is very much working fine with windows XP users but making issue with vista users, it seems to me. If I set properties according to your article, then what are the points I need to take care & what will be the login name to used as sain in figure 6 above.

Thanks in advance!
On Tuesday, June 30, 2009 11:32:11 AM Imar Spaanjaars said:
Hi Puneet,

Outlook probably runs just under the user's credentials so you should use something like DomainName\UserName

Cheers,

Imar
On Monday, July 06, 2009 5:09:37 PM Puneet Singhal said:
Hi Imar,
Thanx for your help, we are able to solve the issue.

but as we have already released the application and users have installed it, can we make it possible that we can provide the script to users through which login can be created automatically & necessary permission can be given on there machine to windows user. and also sql server management studio is to be installed on thier machine.

On Monday, July 06, 2009 6:52:38 PM Imar Spaanjaars said:
Hi Puneet,

Yes, this can all be done through script. Right-click any item in SQL Server and choose Script As.

You need some way to connect to the database so SSMS probably works, although there are also command line tools available to script against SQL Server. Google for "manage sql server command line" to find some useful articles.

Cheers,

Imar
On Thursday, July 16, 2009 7:13:21 AM haile said:
the aspnet db is not copied in the app_code folder
On Friday, December 25, 2009 10:13:28 PM Asghar said:
this wounderful article, realy it solved my problem
thank you so muuuuuuuuuuuuuuuuuch
On Saturday, May 08, 2010 6:27:32 AM hope said:
i tried the Scenario 1 - Using SQL Server Authentication
Login failed for user 'webuser'.  Reason: The password of the account must be changed.

can u plz help me in this matter
im using sqlserver express
thnx
On Saturday, May 08, 2010 7:39:58 AM Imar Spaanjaars said:
Hi there,

Isn't the error message saying what needs to be done: "The password of the account must be changed.".

Looks like you have a password policy in place.

Cheers,

Imar
On Saturday, May 08, 2010 7:48:40 AM hope said:
actully i googled i found that i should login & change the password couse its becouse the passowrd policy
any how after that again i return back to the same problem
Cannot open database "xxxxx" requested by the login. The login failed. Login failed for user 'xxxxxxxxxxxxx'.
On Saturday, May 08, 2010 8:12:44 AM Imar Spaanjaars said:
Then it looks like the account doesn't have the necessary permissions to access the database.

Imar
On Saturday, May 08, 2010 9:38:59 AM hope said:
i chked that through creating new odbc connection from  the administrative tool, using the same account after testing its showing successful
beside why i first created it logged & show its need to change password , when i change password its showing login failled
beside in the server explorer in the asp.net sqlexpress is not showing is it normal or i should do something to make the right connection,
thnx
On Saturday, May 08, 2010 9:42:39 AM Imar Spaanjaars said:
Then there must be something wrong with your conenction string or configuration.

My site is not really a support forum so I can't help you much with this. Would you mind taking this to a forum such as this one: http://p2p.wrox.com/index.php?referrerid=385

Cheers,

Imar
On Saturday, May 08, 2010 9:49:51 AM hope said:
thnx, anyway;)
On Wednesday, May 26, 2010 5:44:55 AM Jiovanni said:
I like your article, very helpful.

I have a question, How I can implement windows authentication with login control in visual studio??
Actually, I am not sure if there are the same thing or I have to implement windows authentication and the login control.
Can you explain me more of this please? can you give me some tips or maybe you have an article related to this...

Thanks for your help...
On Wednesday, May 26, 2010 6:30:23 AM Imar Spaanjaars said:
Hi Jiovanni,

Take a look at the ActiveDirectoryMembershipProvider:

http://msdn.microsoft.com/en-us/library/ms998360.aspx

Cheers,

Imar
On Tuesday, July 20, 2010 10:24:20 AM GM said:
I can't tell you how grateful I am of your website. You have provided helpful articles that no other blogs provide. Please keep up the great work!
On Thursday, November 04, 2010 9:23:30 AM Riz said:
Hi Imar,

Hope you are well. It's been nearly 4 years since I made an initial posts on here on December 7 & 8, 2006. Well I need your advice again please. You might need to go back and re-read the initial posts.

The mirrored accounts solution has worked fine.
Now the Network administrator tried to set up a two-way trust between the two domains. Now my app in one domain can't connect to the database in the other domain anymore. Nothing else has changed.

SQL Server is throwing the error message "Login failed for user 'domain\username' ", where "domain" is the domain in which the web server is, and "username" is the acount that the ASP.NET app is using to connect to SQL Server using Integrated Security.

Do I still need the mirrored user I created in SQL Server? How do I tell SQL Server to trust the 'domain\username' user?

Thanks very much!
On Thursday, November 04, 2010 9:29:49 AM Imar Spaanjaars said:
Hi Riz,

If the two domains trust each other, I think this should work. Otherwise, I don't know the solution.

Cheers,

Imar
On Wednesday, January 26, 2011 9:01:49 PM Mark said:
Great tutorial. I'd like to ask one question. Ideally I'd like to know how to create the Authentication Roles Membership database in SQL Server, without the attaching a pre-made DB. We're developing on VS2008, using FrontPage Extensions to develop down the from a client to the web server. We don't have MS SQL Server Express on our local machines.
On Wednesday, January 26, 2011 11:05:52 PM Imar Spaanjaars said:
Hi Mark,

Check out the post from Scutt Guthrie I linked to at the beginning of this article. You need to run aspnet_regsql to configure a remote database (or alter the LocalSqlServer connection string).

Cheers,

Imar

Talk Back! Comment on Imar.Spaanjaars.Com

I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.

When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.

If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the QuickDocId of the document.

For more information about the Talk Back feature, check out this news item.