导出 (0) 打印
全部展开
此主题尚未评级 - 评价此主题

Forms-Based Authentication with SQL Azure

更新时间: 2011年10月

Author: http://msdn.microsoft.com/zh-cn/library/hh307537.aspx

参考图像

Learn more about RBA Consulting.

Forms-Based Authentication with SQL Azure

This authentication model uses the SqlMembershipProvider and the SqlRoleProvider to authenticate users of an ASP.NET web application that is hosted in Windows Azure against data that is stored in a SQL Azure database.

When to Use the Model

This section looks at the benefits and concerns associated with this model and provides guidance on when the model should be used.

Benefits

  • The SqlMembershipProvider and the SqlRoleProvider is that they ship with the .NET Framework, which means that they have been extensively tested and are officially supported by Microsoft. There is a considerable amount of product documentation and samples available to help developers.

  • User data that is stored in SQL Azure is relational, which makes it easily consumable by other applications and reporting frameworks.

  • There are no transaction costs for using SQL Azure to store user data.

  • If the ASP.NET application (that is hosted in Windows Azure) and the SQL Azure database are hosted in the same data center, the latency between the two is very low.

Concerns

  • When authentication is performed, credentials will be passed over the wire from the client to the ASP.NET application. To prevent these credentials from being compromised, they should be protected during the authentication process by using SSL to secure the communication channel between the client and the server.

  • In cases where an existing user data store does not exist, a new store will have to be built. This means that there is an additional, one-time, administrative cost, which could be significant depending on the number of users in the system.

  • In cases where an existing user store does exist, a migration strategy must be designed and implemented to move the data from the existing store into the SQL Azure store.

  • SQL Azure has size limitations. Currently, SQL Azure limits the size of databases to a maximum of 50 GB. If the database exceeds 50 GB, you must design and implement a partitioning strategy to distribute user data across multiple databases.

  • This model does not allow for run-time changes to authentication logic. As a result, if the ASP.NET application's authentication code requires an update, the application will have to be redeployed to the Windows Azure environment.

  • Connectivity to SQL Azure is much more prone to transient errors compared to on-premises databases. Leveraging the built-in membership classes does not account for such transient conditions. This means it is much more likely to encounter intermittent connectivity errors during authentication. When using SQL Azure in general, the connecting client should implement logic that handles such conditions as per http://blogs.msdn.com/b/appfabriccat/archive/2010/10/28/best-practices-for-handling-transient-conditions-in-sql-azure-client-applications.aspx.

Guidance

  • ASP.NET applications with authentication and authorization requirements that can be defined in terms of user names and roles are a good fit for this model. User names and roles are supported by the SqlMembershipProvider and SqlRoleProvider. If the application has more advanced authentication and authorization requirements, select a different security model.

  • If the ASP.NET application requires analysis of user data, then consider using this model because the relational structure of the data lends itself to complex analysis.

  • It is a good choice if the authentication logic is limited to the scope of ASP.NET application and does not have to be shared with other applications.

  • Consider this model when an existing ASP.NET application that uses the SqlMembershipProvider and the SqlRoleProvider is being moved to the Windows Azure environment. Given that the application is already using these established and well-tested providers, it makes sense to keep them in place.

The Model Defined

The following figure illustrates how forms-based authentication between an ASP.NET application in Windows Azure and a SQL Azure database works.

参考屏幕

The client's browser connects to the ASP.NET application to perform authentication. The connection is made over port 443, which is secured with HTTPS and SSL. All non-secure communication between the client's browser and the ASP.NET application use HTTP, and communicate through port 80.

The ASP.NET application's Web.config file specifies the following information.

  • The application's authentication mode is set to Forms.

  • The logon page that is used by the application for forms authentication.

  • The membership provider is set to the SqlMembershipProvider.

  • The role provider is set to the SqlRoleProvider.

  • The connection string that is used to connect to the SQL Azure user store.

SQL Azure stores the following pieces of information.

  • User data such as user names and encrypted passwords that are used by the ASP.NET application.

  • The names of roles that are used by the ASP.NET application to secure application resources.

  • The associations of users with roles.

The following figure illustrates the schema that is used by SQL Azure to store this information.

参考屏幕

Note that this is the same schema used by SQL Server on-premises.

The ASP.NET application connects to the SQL Azure hosted user store. It uses port 1433, just as it would with any other SQL database. The TDS that is used for application-to-database communication in SQL Azure is encrypted to provide an additional layer of security. If you want finer-grained security, the firewall to SQL Azure can be configured to filter connections based on the requesting application's IP address. By default, the SQL Azure firewall is configured to deny all incoming requests. The firewall can be configured to allow only traffic from other Windows Azure services. This configuration prevents any traffic that is outside of the Windows Azure data center from obtaining a connection to SQL Azure. The firewall can also be configured to allow IP addresses that fall within known ranges to obtain connections to SQL Azure.

How to Implement the Model

This section shows you how to implement forms-based authentication with SQL Azure.

Creating a New Server

The first decision you must make is whether to create a new SQL Azure server, or to use an existing server. This section shows you how to create a new SQL Azure server. If you plan on using an existing server, you can skip ahead to the Configuring an Existing Server section.

  1. Log on to the Windows Azure management portal, which is located at http://manage.windowsazure.com.

  2. Click the Database option on the toolbar on the left side of the screen.



    参考屏幕

  3. Select the subscription that you want to use to create the server.



    参考屏幕

  4. Click the Create button in the Server portion of the toolbar at the top of the screen.



    参考屏幕

  5. The Create Server wizard appears. It prompts you to select the Region where you want the SQL Azure server to be hosted.



    参考屏幕

    Make sure to select the same region as the one that will host the ASP.NET application. Although you could use a different region, your application will encounter latency if you do.

  6. Next, you are prompted to create an administrator account for the server.



    参考屏幕

    There are several requirements for the login and password that you select. The login cannot be one of the following.



    • admin

    • administrator

    • sa

    • root

    • guest

    • dbmanager

    • loginmanger



    The password must be a minimum of eight characters long and contain three out of the four following classes of characters.



    • Latin uppercase letter (A-Z)

    • Latin lowercase letter (a-z)

    • Base 10 digit (0-9)

    • Non-alphanumeric characters



    Additionally, the password may not contain any three consecutive characters of the creator's login.



  7. After you create the administrator account for the server, you are prompted to create firewall rules to enable applications to access the server.



    参考屏幕

  8. The first thing to do is to select the Allow other Windows Azure services to access this server check box. This enables your ASP.NET application, once running in Windows Azure, to connect to the database to perform authentication and authorization.

  9. You will also want to add at least one firewall rule that enables you to connect to the SQL Azure database from your local development machine. You can do this by clicking the Add button.



    参考屏幕

  10. If you simply want to connect from your development machine, you can enter the IP address shown in the Your current IP address field in the IP range start and IP range end fields. If you want to enable multiple machines to access the server from your network, you must enter values in the IP range start and IP range end fields that correspond to valid IP addresses within your network.

  11. After you configure the firewall rules, click Finish. Your server will be provisioned and created.



    参考屏幕

Configuring an Existing Server

If you plan on using an existing SQL Azure server to host the user data store, you need to ensure that the firewall is configured to enable applications to obtain connections. If you are using a new server and configured the server as part of the Creating a New Server section, you can skip ahead to the Creating the Database section. This section will show you how to configure an existing server.

  1. Log on to the Windows Azure management portal, which is located at http://manage.windowsazure.com.

  2. Click the Database option on the toolbar on the left side of the screen.



    参考屏幕

  3. Select the subscription and server that you want to configure.



    参考屏幕

  4. Ensure that the ASP.NET application, once running in Windows Azure, can connect to the SQL Azure server by selecting the Allow other Windows Azure services to access this server check box.



    参考屏幕

  5. Configure the firewall to allow connections from your local development machine or network. To do this, click Add. The Add Firewall Rule dialog box appears.



    参考屏幕

    If you only want to connect from your development machine, you can enter the IP address shown in the Your current IP address field in the IP range start and IP range end fields. If you want to enable multiple machines to access the server from your network, you must enter values in the IP range start and IP range end fields that correspond to valid IP addresses within your network.

  6. Once the firewall is configured, you can go to the Creating the Database section.



    参考屏幕

Creating the Database

The next decision you need to make is whether you will use a new or existing SQL Azure database for the user store. If you created a new server, then you must create a new database. However, if you are using an existing server, you have the option of using an existing database. Although using an existing database can reduce costs, you must monitor the size of the database, and take appropriate actions, such as creating a new database for user data, if the database starts to get close to the maximum size allowed by SQL Azure. Currently, this limit is 50 gigabyte (GB).

This section shows you how to create a new database. If you plan on using an existing database, go to the Preparing the Database section.

  1. Log on to the Windows Azure management portal, which is located at http://manage.windowsazure.com.

  2. Click the Database option on the toolbar on the left side of the screen.



    参考屏幕

  3. Select the subscription, and the server where you want to create the database.



    参考屏幕

  4. Click the Create button in the Database portion of the toolbar at the top of the screen.



    参考屏幕

  5. In the Create Database dialog box that appears, enter a Database name, select an Edition, and a Maximum size, and then click OK.



    参考屏幕

  6. Your database appears in the toolbar on the left side of the screen, under the server where you provisioned it.



    参考屏幕

  7. Before you proceed to the next section, you must get the connection string for the database. Click the Ellipses button (…) next to the View label in the Connection Strings section of the toolbar on the right side of the screen.



    参考屏幕

  8. The Connection Strings dialog box appears that shows the connection strings for ADO.NET, ODBC, and PHP. Your ASP.NET application only needs the ADO.NET version.



    参考屏幕

Preparing the Database

If you are not going to use an existing database, the new database needs to be prepared to store user and role data.

In a standard on-premises implementation, you use the Aspnet_regsql.exe tool to prepare a SQL database so that it can be used by the SqlMembershipProvider and the SqlRoleProvider. The following is an example of the command line that you would use.

aspnet_regsql -S [your server name].database.windows.net -d [your database name] -U [your user name]@[your server name] -P [your password] -A mr

However, if you attempt to run this command line against a SQL Azure database, you receive the following error message.


            An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 40508 and the SqlException message is: USE statement is not supported to switch between databases. Use a new connection to connect to a different Database.
            SQL Server: [your server name].database.windows.net
            Database: [your database name]
            SQL file loaded:
            InstallCommon.sql
          

This error occurs because SQL Azure does not support the USE statement to switch database contexts when it executes commands against SQL Azure databases. To work around this, Microsoft created a hotfix. The following steps show you how to use the tool that is provided by the hotfix.

  1. Download the aspnet_regsqlazure.zip file from the hotfix page.

  2. Extract the contents of the file to a location on your local hard drive.

  3. Open a command prompt and navigate to the directory where you extracted the files.

  4. Use the following command line to execute the tool (substitute your server name, database name, logon, and password where appropriate).

aspnet_regsqlazure -s [your server name].database.windows.net -d [your database name] -u [your user name]@[your server name] -p [your password] -a mr

  1. As the tool executes, it displays various status messages. If you see the following messages in the command window, you will know that the tool has executed properly.

    • Completed execution of InstallCommon.SQL

    • Completed execution of InstallMemgbership.SQL

    • Completed execution of InstallRoles.SQL

Keep in mind that this tool does not have the full capabilities of the Aspnet_regsql.exe tool. First, the tool does not allow you to install the necessary tables that are required to support ASP.NET SQL Session State Management. Second, the tool does not support the -R option. In Aspnet_regsql.exe, the -R command line option can remove features from the SQL database. If you need to remove features from the SQL Azure database, you must do it manually. The following script can be used to delete the views, stored procedures, tables and roles that are associated with the SqlMembershipProvider and SqlRoleProvider.

DROP VIEW [dbo].[vw_aspnet_Applications]
DROP VIEW [dbo].[vw_aspnet_MembershipUsers]
DROP VIEW [dbo].[vw_aspnet_Roles]
DROP VIEW [dbo].[vw_aspnet_Users]
DROP VIEW [dbo].[vw_aspnet_UsersInRoles]
GO
DROP PROCEDURE [dbo].[aspnet_AnyDataInTables]
DROP PROCEDURE [dbo].[aspnet_Applications_CreateApplication]
DROP PROCEDURE [dbo].[aspnet_CheckSchemaVersion]
DROP PROCEDURE [dbo].[aspnet_Membership_ChangePasswordQuestionAndAnswer]
DROP PROCEDURE [dbo].[aspnet_Membership_CreateUser]
DROP PROCEDURE [dbo].[aspnet_Membership_FindUsersByEmail]
DROP PROCEDURE [dbo].[aspnet_Membership_FindUsersByName]
DROP PROCEDURE [dbo].[aspnet_Membership_GetAllUsers]
DROP PROCEDURE [dbo].[aspnet_Membership_GetNumberOfUsersOnline]
DROP PROCEDURE [dbo].[aspnet_Membership_GetPassword]
DROP PROCEDURE [dbo].[aspnet_Membership_GetPasswordWithFormat]
DROP PROCEDURE [dbo].[aspnet_Membership_GetUserByEmail]
DROP PROCEDURE [dbo].[aspnet_Membership_GetUserByName]
DROP PROCEDURE [dbo].[aspnet_Membership_GetUserByUserId]
DROP PROCEDURE [dbo].[aspnet_Membership_ResetPassword]
DROP PROCEDURE [dbo].[aspnet_Membership_SetPassword]
DROP PROCEDURE [dbo].[aspnet_Membership_UnlockUser]
DROP PROCEDURE [dbo].[aspnet_Membership_UpdateUser]
DROP PROCEDURE [dbo].[aspnet_Membership_UpdateUserInfo]
DROP PROCEDURE [dbo].[aspnet_RegisterSchemaVersion]
DROP PROCEDURE [dbo].[aspnet_Roles_CreateRole]
DROP PROCEDURE [dbo].[aspnet_Roles_DeleteRole]
DROP PROCEDURE [dbo].[aspnet_Roles_GetAllRoles]
DROP PROCEDURE [dbo].[aspnet_Roles_RoleExists]
DROP PROCEDURE [dbo].[aspnet_Setup_RemoveAllRoleMembers]
DROP PROCEDURE [dbo].[aspnet_UnRegisterSchemaVersion]
DROP PROCEDURE [dbo].[aspnet_Users_CreateUser]
DROP PROCEDURE [dbo].[aspnet_Users_DeleteUser]
DROP PROCEDURE [dbo].[aspnet_UsersInRoles_AddUsersToRoles]
DROP PROCEDURE [dbo].[aspnet_UsersInRoles_FindUsersInRole]
DROP PROCEDURE [dbo].[aspnet_UsersInRoles_GetRolesForUser]
DROP PROCEDURE [dbo].[aspnet_UsersInRoles_GetUsersInRoles]
DROP PROCEDURE [dbo].[aspnet_UsersInRoles_IsUserInRole]
DROP PROCEDURE [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]
GO
DROP TABLE [dbo].[aspnet_SchemaVersions]
DROP TABLE [dbo].[aspnet_UsersInRoles]
DROP TABLE [dbo].[aspnet_Membership]
DROP TABLE [dbo].[aspnet_Users]
DROP TABLE [dbo].[aspnet_Roles]
DROP TABLE [dbo].[aspnet_Applications]
GO
DECLARE @RoleName sysname
set @RoleName = N'aspnet_Membership_BasicAccess'
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = 'R')
Begin
     DECLARE @RoleMemberName sysname
     DECLARE Member_Cursor CURSOR FOR
     select [name]
     from sys.database_principals 
     where principal_id in ( 
          select member_principal_id 
          from sys.database_role_members 
          where role_principal_id in (
               select principal_id
               FROM sys.database_principals where [name] = @RoleName  AND type = 'R' ))
     OPEN Member_Cursor;
     FETCH NEXT FROM Member_Cursor
     into @RoleMemberName
     WHILE @@FETCH_STATUS = 0
     BEGIN
          exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName
          FETCH NEXT FROM Member_Cursor
          into @RoleMemberName
     END;
     CLOSE Member_Cursor;
     DEALLOCATE Member_Cursor;
End
GO
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'aspnet_Membership_BasicAccess' AND type = 'R')
DROP ROLE [aspnet_Membership_BasicAccess]
GO
DECLARE @RoleName sysname
set @RoleName = N'aspnet_Membership_FullAccess'
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = 'R')
Begin
     DECLARE @RoleMemberName sysname
     DECLARE Member_Cursor CURSOR FOR
     select [name]
     from sys.database_principals 
     where principal_id in ( 
          select member_principal_id 
          from sys.database_role_members 
          where role_principal_id in (
               select principal_id
               FROM sys.database_principals where [name] = @RoleName  AND type = 'R' ))
     OPEN Member_Cursor;
     FETCH NEXT FROM Member_Cursor
     into @RoleMemberName
     WHILE @@FETCH_STATUS = 0
     BEGIN
          exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName
          FETCH NEXT FROM Member_Cursor
          into @RoleMemberName
     END;
     CLOSE Member_Cursor;
     DEALLOCATE Member_Cursor;
End
GO
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'aspnet_Membership_FullAccess' AND type = 'R')
DROP ROLE [aspnet_Membership_FullAccess]
GO
DECLARE @RoleName sysname
set @RoleName = N'aspnet_Membership_ReportingAccess'
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = 'R')
Begin
     DECLARE @RoleMemberName sysname
     DECLARE Member_Cursor CURSOR FOR
     select [name]
     from sys.database_principals 
     where principal_id in ( 
          select member_principal_id 
          from sys.database_role_members 
          where role_principal_id in (
               select principal_id
               FROM sys.database_principals where [name] = @RoleName  AND type = 'R' ))
     OPEN Member_Cursor;
     FETCH NEXT FROM Member_Cursor
     into @RoleMemberName
     WHILE @@FETCH_STATUS = 0
     BEGIN
          exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName
          FETCH NEXT FROM Member_Cursor
          into @RoleMemberName
     END;
     CLOSE Member_Cursor;
     DEALLOCATE Member_Cursor;
End
GO
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'aspnet_Membership_ReportingAccess' AND type = 'R')
DROP ROLE [aspnet_Membership_ReportingAccess]
GO
DECLARE @RoleName sysname
set @RoleName = N'aspnet_Roles_BasicAccess'
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = 'R')
Begin
     DECLARE @RoleMemberName sysname
     DECLARE Member_Cursor CURSOR FOR
     select [name]
     from sys.database_principals 
     where principal_id in ( 
          select member_principal_id 
          from sys.database_role_members 
          where role_principal_id in (
               select principal_id
               FROM sys.database_principals where [name] = @RoleName  AND type = 'R' ))
     OPEN Member_Cursor;
     FETCH NEXT FROM Member_Cursor
     into @RoleMemberName
     WHILE @@FETCH_STATUS = 0
     BEGIN
          exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName
          FETCH NEXT FROM Member_Cursor
          into @RoleMemberName
     END;
     CLOSE Member_Cursor;
     DEALLOCATE Member_Cursor;
End
GO
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'aspnet_Roles_BasicAccess' AND type = 'R')
DROP ROLE [aspnet_Roles_BasicAccess]
DECLARE @RoleName sysname
set @RoleName = N'aspnet_Roles_FullAccess'
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = 'R')
Begin
     DECLARE @RoleMemberName sysname
     DECLARE Member_Cursor CURSOR FOR
     select [name]
     from sys.database_principals 
     where principal_id in ( 
          select member_principal_id 
          from sys.database_role_members 
          where role_principal_id in (
               select principal_id
               FROM sys.database_principals where [name] = @RoleName  AND type = 'R' ))
     OPEN Member_Cursor;
     FETCH NEXT FROM Member_Cursor
     into @RoleMemberName
     WHILE @@FETCH_STATUS = 0
     BEGIN
          exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName
          FETCH NEXT FROM Member_Cursor
          into @RoleMemberName
     END;
     CLOSE Member_Cursor;
     DEALLOCATE Member_Cursor;
End
GO
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'aspnet_Roles_FullAccess' AND type = 'R')
DROP ROLE [aspnet_Roles_FullAccess]
GO
DECLARE @RoleName sysname
set @RoleName = N'aspnet_Roles_ReportingAccess'
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = 'R')
Begin
     DECLARE @RoleMemberName sysname
     DECLARE Member_Cursor CURSOR FOR
     select [name]
     from sys.database_principals 
     where principal_id in ( 
          select member_principal_id 
          from sys.database_role_members 
          where role_principal_id in (
               select principal_id
               FROM sys.database_principals where [name] = @RoleName  AND type = 'R' ))
     OPEN Member_Cursor;
     FETCH NEXT FROM Member_Cursor
     into @RoleMemberName

     WHILE @@FETCH_STATUS = 0
     BEGIN
          exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName
          FETCH NEXT FROM Member_Cursor
          into @RoleMemberName
     END;
     CLOSE Member_Cursor;
     DEALLOCATE Member_Cursor;
End
GO
DROP ROLE [aspnet_Roles_ReportingAccess]
GO

Creating the ASP.NET Application

After you have prepared the SQL Azure database, you must create the ASP.NET application that will use the database.

  1. Open Visual Studio and create a new Windows Azure Project.



    参考屏幕

  2. Add an ASP.NET Web Role to the project.



    参考屏幕

  3. Open the ASP.NET Web Role project's Web.config file, and find the following entry.

    <add name="ApplicationServices" connectionString="data
    source=.\SQLEXPRESS;Integrated
    Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User
    Instance=true" providerName="System.Data.SqlClient" />
    
  4. Replace the entry with the following (substitute your server name, database name, logon, and password where appropriate).

    <add name="ApplicationServices" connectionString="Server=tcp:[your
    server name].database.windows.net,1433;Database=[your database 
    name];User ID=[your login name]@[your server name];Password=[your password];Trusted_Connection=False;Encrypt=True;"
    providerName="System.Data.SqlClient" />
    
    There is nothing else you need to do to configure the ASP.NET application to use SqlMembershipProvider and SqlRoleProvider. However, you should test your application to make sure that everything is working as expected.

  5. From Visual Studio, start your application.

  6. Click the Log In link that is on the right side of the page that appears.



    参考屏幕

  7. On the next screen, click the Register link.



    参考屏幕

  8. Fill out the fields on the Create a New Account page, and click the Create User button.



    参考屏幕

  9. If everything works as expected, you will see the user name that you entered on the registration page in the upper right corner of the page you are redirected to when registration is complete.



    参考屏幕

Migrating Existing User Data

You may want to migrate an existing on-premises user store if you are moving an on-premises ASP.NET application to the Windows Azure platform. The following sections show two approaches to migrate an existing SQL Server database to SQL Azure.

Migrating Existing User Data with "Generate and Publish Scripts"

The first approach uses the "Generate and Publish Scripts" functionality that is part of SQL Server 2008 R2 Management Studio.

  1. Open SQL Server 2008 R2 Management Studio and connect to the server that contains the database that you want to migrate.



    参考屏幕

  2. In the Object Explorer, right-click the database you want to migrate, select Tasks, and select Generate Scripts…



    参考屏幕

  3. Click Next on the Introduction screen of the Generate and Publish wizard. If you have used this wizard in the past, you may have selected the Do not show this page again option, in which case this screen will not appear.



    参考屏幕

  4. On the Choose Objects screen, you will need to select the tables in the database that contain the user and role data that you want to export. If the database only contains user and role data, you can select the Select specific database objects option button, and check the check box next to the Tables node of the tree view.



    参考屏幕

    If the database contains additional tables, you will need to select each table that contains user and role data individually. These are the necessary tables.



    • dbo.aspnet_Applications

    • dbo.aspnet_Membership

    • dbo.aspnet_Roles

    • dbo.aspnet_SchemaVersions

    • dbo.aspnet_Users

    • dbo.aspnet_UsersInRoles



    参考屏幕

  5. After selecting the appropriate tables, click the Next button. The Set Scripting Options screen appears. To generate data-only scripts that are compatible with SQL Azure, you must configure two advanced options. Click Advanced.

  6. The first option is the Script for the database engine type option. Change this from Stand-alone instance to SQL Azure Database.



    参考屏幕

  7. Because the schema has already been installed in the SQL Azure database, you only need to script the data. To specify data-only scripting, change the Types of data to script option from Schema only to Data only. Click OK.



    参考屏幕

  8. Once these options are set, click Next on the Set Scripting Options page, and click Next on the Summary page.

  9. The Generate and Publish Scripts wizard generates the specified scripts. The duration of this process depends on how much data is in each table. The more data in the tables, the longer the process takes. Once the process completes, the script can be executed against a SQL Azure database.

Although the Generate and Publish Scripts wizard may appear to be a straightforward option for data migration, there is one thing to keep in mind. The wizard creates an INSERT statement for each row in each table that is being migrated. This is not ideal for moving large amounts of data. The impact on performance of many individual INSERT statements is substantial.

Migrating Existing User Data with bcp

An alternative to the Generate and Publish Scripts wizard is the bcp command-line tool. The bcp utility copies data between a table in a SQL Server database and a file. You can use bcp to copy data from tables in an on-premises SQL Server database to a series of files. You can then use bcp to copy the data from these files to a SQL Azure database. The bcp command-line utility can be found in the following location.

[install drive]:\Program Files\Microsoft SQL Server\100\Tools\Binn

The first step is to export data from the existing SQL Server database to a file. You must export data from the following tables.

  • dbo.aspnet_Applications

  • dbo.aspnet_Membership

  • dbo.aspnet_Roles

  • dbo.aspnet_SchemaVersions

  • dbo.aspnet_Users

  • dbo.aspnet_UsersInRoles

To perform the export, use the following bcp scripts.


            bcp [on-premises db name].dbo.aspnet_Applications out aspnet_Applications.dat -n -S [on-premises SQL Server name] -T
            bcp [on-premises db name].dbo.aspnet_Membership out aspnet_Membership.dat -n -S [on-premises SQL Server name] -T
            bcp [on-premises db name].dbo.aspnet_Roles out aspnet_Roles.dat -n -S [on-premises SQL Server name] -T
            bcp [on-premises db name].dbo.aspnet_Users out aspnet_Users.dat -n -S [on-premises SQL Server name] -T
            bcp [on-premises db name].dbo.aspnet_UsersInRoles out aspnet_UsersInRoles.dat -n -S [on-premises SQL Server name] -T
          

After the data is exported from the on-premises SQL Server database, use bcp to import the data into SQL Azure. To avoid errors caused by referential integrity, the tables must be imported in the following order.

  1. dbo.aspnet_Applications

  2. dbo.aspnet_Users

  3. dbo.aspnet_Roles

  4. dbo.aspnet_Membership

  5. dbo.aspnet_UsersInRoles

To perform the import to SQL Azure, use the following bcp scripts.

bcp [SQL Azure db name].dbo.aspnet_Applications in 
aspnet_Applications.dat -n -S [SQL Azure server name].database.windows.net -U 
[SQL Azure login]@[SQL Azure server name] -P [SQL Azure password] -E

bcp [SQL Azure db name].dbo.aspnet_Users in aspnet_Users.dat -n -S [SQL Azure 
server name].database.windows.net -U [SQL Azure login]@[SQL Azure server 
name] -P [SQL Azure password] -E

bcp [SQL Azure db name].dbo.aspnet_Roles in aspnet_Roles.dat -n -S [SQL Azure 
server name].database.windows.net -U [SQL Azure login]@[SQL Azure server 
name] -P [SQL Azure password] -E


bcp [SQL Azure db name].dbo.aspnet_Membership in aspnet_Membership.dat -n -S
[SQL Azure server name].database.windows.net -U [SQL Azure login]@[SQL Azure 
server name] -P [SQL Azure password] -E

bcp [SQL Azure db name].dbo.aspnet_UsersInRoles in aspnet_UsersInRoles.dat -n 
-S [SQL Azure server name].database.windows.net -U [SQL Azure login]@[SQL 
Azure server name] -P [SQL Azure password] -E

It is important to note that bcp has no knowledge of schema. It only knows data, which means that the schema that is required for user and role data must be installed in the SQL Azure database before you Import the data.

本文是否对您有所帮助?
(1500 个剩余字符)
感谢您的反馈
显示:
© 2014 Microsoft. 版权所有。