SQL Server Reporting Services (SSRS)

Cloud SQL supports SQL Server Reporting Services (SSRS) which helps you create, deploy, and manage paginated reports.

This page provides the prerequisites, setup procedure, and limitations for using SSRS in Cloud SQL.

Before you begin

Before using SSRS on Cloud SQL, you must have the following:

  • A Cloud SQL instance to host the report server database
  • A host where you install and run SSRS. It can be either a Compute Engine VM or a host that doesn't use Compute Engine. For installation instructions, see Install SQL Server Reporting Services.

  • A Cloud DNS record to assign a hostname for your Cloud SQL instance. You can create a hostname by using one of the following options:

    • Cloud DNS: Use Cloud DNS to create a hostname that points to your instance.
    • Custom DNS zone: If you manage your own DNS zone, then create an A record that maps your preferred hostname to the IP address of your instance.
    • Local hostname mapping: Modify your system's hosts file to create a custom alias for your instance.

  • Two existing Cloud SQL logins, a setup login and a service login. To create a login, see gcloud sql users create.

    If you want to use Windows authentication, then you must use Managed Active Directory to join the SQL Server instance and the host running SSRS. For more information, see Use Managed Microsoft AD with Cloud SQL.

Procedure

To set up SSRS in Cloud SQL, perform the following steps:

  1. Acquire an SSRS lease.
  2. Set up SSRS.
  3. Release an SSRS lease.

Acquire an SSRS lease

SSRS requires elevated permissions during setup. To acquire an SSRS lease for your instance, use the following command:

gcloud

Use the following command to acquire a lease:

gcloud sql instances acquire-ssrs-lease INSTANCE_NAME \
--project=PROJECT_NAME \
--setup-login=SETUP_LOGIN \
--service-login=SERVICE_LOGIN \
--report-database=REPORT_DATABASE \
--duration=DURATION \

The following table describes this operation's gcloud parameters:

Parameter Description Allowed values Default value
--setup-login Required. The login used to connect to the instance during SSRS setup. This is the first login that is requested in Report Server Configuration Manager when setting a report server database. This must be a existing SQL Server login, such as sqlserver None
--service-login Required. The SQL Server login used by report server to connect to the report server database during SSRS setup. This is the second login that is requested in Report Server Configuration Manager when setting a report server database. This must be an existing SQL Server login and must be different from the setup login. None.
--report-database Required. An existing or a new report database name on the instance. Certain special characters are not allowed in the report database name, such as spaces, brackets, non-English characters. None
--duration Optional. The number of hours that the SSRS lease is active during which you can perform the SSRS setup. Between 1 and 12 hours. For example, 4h 5 hours

REST v1

Using the REST API, you can acquire an SSRS lease on an instance. To acquire an SSRS lease, specify the following:
  • Two different SQL Server logins, the setup login and service login
  • The report database name
  • The duration for which the lease is active

Only the DURATION field is optional. For more information, see AcquireSsrsLeaseContext:

  • PROJECT_ID: the ID of the project.
  • INSTANCE_NAME: the name of the instance.
  • SETUP_LOGIN: the SQL Server login that's used to connect to the SQL Server instance during SSRS setup. This the first login that is requested in Report Server Configuration Manager when setting a report server database.
  • SERVICE_LOGIN: the SQL Server login that's used by report server to connect to the report server database during SSRS setup. This is the second login that is requested in Report Server Configuration Manager when setting a Report Server Database.
  • REPORT_DATABASE: an existing or a new report database name on the instance. Microsoft doesn't allow certain special characters in the report database name, such as spaces, brackets, non-English characters.
  • DURATION: Optional. The number of hours that the SSRS lease is active during which you can perform the SSRS setup. Between 1 and 12 hours, for example, 4h.

HTTP method and URL: 

POST https://sqladmin.googleapis.com/sql/v1/projects/PROJECT_ID/instances/INSTANCE_NAME/acquireSsrsLease

Request JSON body: 

{
  "acquireSsrsLeaseContext": {
    "setupLogin": SETUP_LOGIN,
    "serviceLogin": SERVICE_LOGIN,
    "reportDatabase": REPORT_DATABASE,
    "duration": DURATION,
  }
}

REST v1beta4

Using the REST API, you can acquire an SSRS lease on an instance. To acquire an SSRS lease, specify the following:
  • Two different SQL Server logins, the setup login and service login
  • The report database name
  • The duration for which the lease is active

Only the DURATION field is optional. For more information, see AcquireSsrsLeaseContext:

  • PROJECT_ID: the ID of the project.
  • INSTANCE_NAME: the name of the instance.
  • SETUP_LOGIN: the SQL Server login that's used to connect to the SQL Server instance during SSRS setup. This the first login that is requested in Report Server Configuration Manager when setting a report server database.
  • SERVICE_LOGIN: the SQL Server login that's used by report server to connect to the report server database during SSRS setup. This is the second login that is requested in Report Server Configuration Manager when setting a Report Server Database.
  • REPORT_DATABASE: an existing or a new report database name on the instance. Microsoft doesn't allow certain special characters in the report database name, such as spaces, brackets, non-English characters.
  • DURATION: Optional. The number of hours that the SSRS lease is active during which you can perform the SSRS setup. Between 1 and 12 hours, for example, 4h.

HTTP method and URL: 

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME/acquireSsrsLease

Request JSON body: 

{
  "acquireSsrsLeaseContext": {
    "setupLogin": SETUP_LOGIN,
    "serviceLogin": SERVICE_LOGIN,
    "reportDatabase": REPORT_DATABASE,
    "duration": DURATION,
  }
}

Set up SSRS

To perform the SSRS setup, follow these steps:

  1. Open Reporting Services Configuration Manager on the host where SSRS is installed.
  2. Connect to your local report server instance.
  3. Click Change Database.
    1. Enter the SQL Server hostname created in Cloud DNS and the SQL Server login credentials for the setup login used for the SSRS lease.
    2. Enter the name of a new report server database or existing report database used for the SSRS lease.
    3. Enter the SQL Server login credentials for the service login used for the SSRS lease.
    4. Click Next and finish through the last step.
  4. If not already set, then configure the Web Service URL and Web Portal URL. The Web Portal should load successfully within a few seconds.

Release an SSRS lease

You can release a lease manually, or you can wait until the lease automatically expires. Releasing the lease is required to revert any elevated permissions needed for setup and to allow the service login to connect to the report database. The service login can't access the report database until the lease expires or is released manually. The following command releases an SSRS lease.

gcloud

Use the following command to release a lease:

gcloud sql instances release-ssrs-lease INSTANCE_NAME \
--project=PROJECT_NAME

REST v1

Using the REST API, you can release an SSRS lease for an instance. As shown in the following request example, you only need to provide the instance name and project ID.

  • PROJECT_ID: the ID of the project.
  • INSTANCE_NAME: the name of the instance.

HTTP method and URL: 

POST https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME/releaseSsrsLease

REST v1beta4

Using the REST API, you can release an SSRS lease for an instance. As shown in the following request example, you only need to provide the instance name and project ID.

  • PROJECT_ID: the ID of the project.
  • INSTANCE_NAME: the name of the instance.

HTTP method and URL: 

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME/releaseSsrsLease

Limitations

  • Active Directory logins are not supported for setting up the report database.
  • The report database name can contain only alphanumeric characters and hyphens.
  • You must acquire a lease for changing the login used to connect to the report database through Report Server Configuration Manager.
  • For a database that is processed by a service login, the import sql files and delete replicated database operations can fail between the completion of the SSRS setup and the release of the lease.
  • You must delete and recreate the database user for the service login on an imported report database. This is because the existing user in the imported database doesn't belong to the new instance.
  • When adding new report servers for a scale-out deployment in SSRS, you must acquire and release the SSRS lease for every new Windows VM hosting a report server. Each Cloud SQL instance requires that you configure the new report database by using the existing report database.
  • You must create a new lease every time you want to change the login credentials for the report server database in Report Server Configuration Manager.