Question:
What rights to the SQL Server are required by ICTool?
Answer:
By far, the easiest way to satisfy the list of rights required now and any time in the future is to make the Windows user account that is running ICTool an SA equivalent on the SQL Server. Of course, this is not always practical. ICTool uses Windows authentication, so make note of the credentials used to log into the IIS server to run ICTool.
These credentials must be valid to log into the SQL Server Security and provide
- Database Roles:
- DB_OWNER role for the Spitfire, External Sys, and Report Manager databases
- DB_READER role for
- MSDB – To inquire about agent jobs
- MASTER – to inquire about logins
- GRANT CONTROL SERVER TO [DomainUser]
- For randomizing passwords without knowing prior passwords
- Includes: GRANT ALTER TRACE to [DomainUser] – for SQL Profiler
- Server Roles: (if not SYSADMIN), in order of ‘need‘
- SecurityAdmin – Can alter logons. (Required every time ICTool applies an update)
- ProcessAdmin – Can alter connections. (Very handy if there is a locking contention issue, fairly low risk)
- DBCreator – Can create databases. (Required when creating new sites or Archive databases)
- SetupAdmin – Can alter linked servers. (Required for managing report server integration).
- ServerAdmin – Can alter Microsoft SQL Server settings, shut down the SQL Server Service, create and alter endpoints, alter the SQL Server state, and alter resources.
- DiskAdmin – Can alter resources. (Required if we have to troubleshoot disk, move LDF files, etc.)
Additional Comments:
Other than SecurityAdmin, additional server roles can be applied and removed as needed.
If unable to connect using windows authentication, ICTool will attempt to use the SQL authenticated login “SpitfireICTool”. ICTool creates and stores a long random password for this login and saves an encrypted copy to its configuration file. Use this password in SQL Management Studio and do not require SQL password changes. We used this approach because (left to their own choice) too many sites generated insufficiently secure passwords. So, no, you do not get a choice.
KBA-01405; Last updated: September 20, 2017 at 9:23 am;