Granting Access to a SQL Server Database

  • Article

To use remote data access (RDA), you must grant access to the Microsoft SQL Server database based on how Microsoft Internet Information Services (IIS) and SQL Server Authentication are configured. This step can be completed by implementing RDA programmatically.

The following table lists the database access that must be granted for the different IIS authentication modes and SQL Server Authentication modes used.

IIS authentication mode SQL Server authentication mode Must grant database access to

Anonymous access

Integrated Windows Authentication

computername\IUSR_computername or the configured IIS anonymous user account

Basic authentication

Integrated Windows Authentication

The IIS client's user or group.

Integrated Windows Authentication

Integrated Windows Authentication

The IIS client's user or group. This configuration is supported only if SQL Server and IIS are running on the same computer.

Anonymous access, Basic authentication, or Integrated Windows Authentication

SQL Server Authentication

The user specified in the SQL Server OLEDBConnectionString parameter of the Pull, Push, or SubmitSQL method in the RDA object.

For example, if you are using anonymous access as the IIS authentication mode and Integrated Windows Authentication, you will have to grant database access to *computername\IUSR_computername,*which is supported only if the computer is running both SQL Server and IIS because this is not a domain account, or the configured IIS anonymous user account.

To configure database access

  1. On the computer that is running SQL Server, start SQL Server Management Studio. In the Registered Servers pane, double-click SQL Server.
  2. In the object explorer pane, expand SQL Server, expand the Security folder, right-click Logins, and then click New Login.
  3. In the Login – New dialog box, specify either Windows Authentication or SQL Server Authentication mode.
    1. If you are using Windows Authentication, enter a logon name and select either the Grant Access or the Deny Access option.
    2. If you are using SQL Server Authentication, type a logon name and password, and then confirm the password.
  4. In the left pane, click Database Access.
  5. In the right pane, select the Permit check box for the databases you are granting access to, and then click OK.