edit-icon download-icon

Create account and database for SQL Server 2012 and 2016

Last Updated: Dec 18, 2017

Note: This document is applicable only to SQL Server 2012 instances. For more information on how to create database and account for SQL Server 2008 R2 instance, see Create database and account for SQL Server 2008 R2.

Before RDS can be used, a database and an account must be created for the RDS instance. For SQL Server 2012 instance, you must create an initial account on the RDS console. Then you can create and manage the databases through a client. This document takes the client Microsoft SQL Server Management Studio (SSMS) as an example to introduce how to create databases and accounts for SQL Server 2012 and 2016 instances.

Background information

  • Databases under a single instance share all the resources of this instance. Each SQL Server 2012 and 2016 instance supports one initial account, countless general accounts, and countless databases. You must create and manage the general accounts and databases through SQL statements.

  • To migrate the local database to RDS, you must create the same database and account in RDS instance as those of the local database.

  • When assigning account permissions for each database, follow the minimum permission’ principle and service roles to create accounts and rationally assign Read-only and Read/Write permissions. When necessary, you can split accounts and databases into smaller units so that each account can only access data for its own services. If the account does not need to write data to a database, assign Read-only permission.

  • For database security, set strong passwords for the accounts and change the passwords regularly.

Procedure

  1. Log on to the RDS console.

  2. Select the region where the target instance is located.

  3. Click the ID of the instance to visit the Basic Information page.

  4. In the left-side navigation pane, select Account Management to visit the Account Management page.

  5. Click Create Initial Account, as shown in the following figure.

    Create initial account

  6. To create an account, enter the relevant details in the required fields.

    Initial account information

    Parameters description:

    • Database Account: refers to the name of the initial account. It can have 2 to 16 characters including the lower-case letters, digits, or underscores (_). It must begin with a letter and end with a letter or digit.

    • Password: refers to the password corresponding to the initial account. It can have 8 to 32 characters including at least three of the following:

      • Capital letters

      • Lower-case letters

      • Digits

      • Special characters (!@#$%^&*()_-+=)

    • Re-enter Password: re-enter the password to make sure the password is entered correctly.

  7. Click OK, and the initial account is created.

  8. Add the IP address accessing the RDS instance to RDS whitelist. For more information on how to set whitelist, see Set the whitelist.

  9. Start the Microsoft SQL Server Management Studio client.

  10. Enter the connection information, as shown in the following figure.

    Parameters description:

    • Server type: select Database Engine.

    • Server name: consists of the Internet/intranet address and the correponding port number of the RDS instance. The connection address and the port number must be separated by a comma, for example, rm-bptest.sqlserver.rds.aliyuncs.com,3433. The following shows the procedure to view the connection address and the port information of the RDS instance.

      1. Log on to the RDS console.

      2. Select the region where the target instance is located.

      3. Click the ID of the instance to visit the Basic Information page.

      4. In the Basic Information area, you can find the Internet/intranet address and Internet/intranet port number of the instance, as shown in the following figure.

        View the basic information of the instance

    • Authentication: select SQL Server Authentication.

    • Login: refers to the initial account name of the RDS instance.

    • Password: refers to the password corresponding to the initial account of the RDS instance.

  11. Click Connect.

  12. Right click Databases, and then select New Database.

  13. In the New Database window, select the General tab page.

  14. Enter the name of the new database in the Database name field, and then click OK.

    Name the database

  15. When the new database is created successfully, you can find it in Databases, as shown in the following figure.

    Note: We do not recommend that you do any operations in the default System Databases.

  16. Select Security, right click Logins, and then select New Login to create a general account in the RDS instance, as shown in the following figure.

  17. Enter the name and password of the new account, and select the default database, as shown in the following figure.

    Note: Select SQL Server authentication and adjust the other password policies based on your business requirements.

    Enter the information of new account

  18. Click OK.

  19. When the new account is created successfully, you can find it in Security > Logins, as shown in the following figure.

  20. Double click the new account to set its properties. You can authenticate this account on the Server Roles tab page and bind it to certain databases on the User Mapping tab page, as shown in the following figure.

    Set user's properties

  21. Click OK.

Thank you! We've received your feedback.