How To Set Service Account on Gateway
- Ann Crawford (Unlicensed)
- Steven Maglio
- Mark Norstedt
Service accounts contain passwords that do not expire. They are domain accounts used to connect to sql server data and files. On Premise gateway connections will be configured with windows security to access sql databases and files. Password for the accounts are stored in secret server.
Instructions
Ensure that the ID used in the gateway connection has sql server access to view or execute SQL data objects.
USE [master]
GO
CREATE LOGIN [sa\IUSR_Bi_ISISTest] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GOadd user to the database CREATE USER [<service account >] FOR LOGIN [<service account>]
add user to an db role
grant access to the role for the required dataset or data flow.
Configure Gateway connection to the data source.
Enter the user id based on the database and environment. Password for the accounts are stored in secret server.
The authentication should be windows
Set the Privacy Level to Organizational
3. Use the following slack command to create new service accounts
a. sysbot serviceaccount create <domainaccount{env}> <owners=REGS|FAID|ADMS|SSIS|ENT|DATA|GDIV|LSAA> <domain=myucsb|sa> <description> - Request New Service Account
Here is an example : sysbot serviceaccount create IUSR_Bi_ISIS owners=SSIS sa "Service account for Power BI Account application"
Users accounts must be 15 chars or less not including the environment. The sysbot command creates an account for dev, test and prod.
More information on sysbot for service accounts and permissions.
To get a list of all sys bot commands @sysbot help
sysbot db create <dbname> <owners=REGS|FAID|ADMS|SSIS|ENT|DATA|GDIV|LSAA> “[notes]” - Request New Database
sysbot db guidelines - Info about Service Accounts
sysbot serviceaccount create <domainaccount{env}> <owners=REGS|FAID|ADMS|SSIS|ENT|DATA|GDIV|LSAA> <domain=myucsb|sa> <description> - Request New Service Account
for example to create a service account for identitystaging
@sysbot serviceaccount create IUSR_Bi_IdStg owners=DATA sa "Service account for PBI identitystaging
sysbot db permission create <dbname> <domainaccount> <role> <createrole=true|false> - Request New Database Permission
Please note that within the domainaccount name {env} should not be set to a value. It should either be omitted our used as the literal value of ‘{env}’.
Database | Environment | User ID |
|---|---|---|
IdentityStaging | Test Prod | sa\IUSR_Bi_IdStgTest sa\IUSR_Bi_IdStg |
ProSam | Test Test (GradDiv) Prod Prod (GradDiv) | sa\IUSR_Bi_ProSmTest sa\SQLReportingPrSmTest sa\IUSR_Bi_ProSm sa\SQLReportingPrSm |
ISIS | Test Prod Prod (GradDiv Finance Ops) | sa\IUSR_Bi_ISISTest sa\IUSR_Bi_ISIS sa\ISVC_GradRpt |
CAPS-Custom | Test Prod | sa\IUSR_Bi_CAPSCTest sa\IUSR_Bi_CAPSC |
SHS-Custom | Test Prod | sa\IUSR_Bi_SHSCTest sa\IUSR_Bi_SHSC |
GradDiv | Test (GradDiv Finance Ops) Prod (GradDiv Finance Ops) | sa\SQLReportingPrSmTest sa\SQLReportingPrSm |
Graddiv_Services | Test Prod (GradDiv Finance Ops) | sa\SQLReportingPrSmTest sa\SQLReportingPrSm |
SADW | Test Test (GradDiv Finance Ops) Prod Prod (GradDiv Finance Ops) | sa\ sa\SQLReportingPrSmTest sa\ sa\SQLReportingPrSm |
Create ReportingRole and assign the system account
database | sysbot |
|---|---|
ISIS | sysbot db permission create ISIS IUSR_Bi_ISIS ReportingRole createrole=true |
ProSam | sysbot db permission create ProSam IUSR_Bi_ProSm ReportingRole createrole=true |
|
|
|
|
|
|