Problem:
Customer would like to use the External SQL Database feature for Configuration and Batch Manager. You assist them with trying to complete a hard-coded connection string. The server cannot be found and/or you are not able to create a SQL Database.
Solution:
There are two criteria that are critical to a successful connection string, formatting and permissions.
- Open attached Document that shows a test example of Service Manager and SQL Server 2012 Management Studio. (SQL Management Diagram(Solution 166).zip)
- Notice how the connection string "Data Source=WORKMANDESKTOP\SQLEXPRESS" Directly matches the name of the Master Database. Ensure that whether you are testing or in a user's environment you are matching the Master Database for your Data Source.
- [3]I am using a hard-coded example to show you how you would implement the username into the connection string.
- To create a Login open SQL Server Management Studio 2012.
- Select [Security][Logins][Right Click on Logins][New Login...].
- Select SQL Server authentication and create a login name and password.
- Ensure to turn off the following options.
- Enforce Password Policy.
- User must change password at next login.
- Click OK.
- Once completed you should see the login name present under [Security][Logins]
- Next right click on the Master Database. [Properties][Permissions][Select Login Name][Explicit]
- Grant the Following permissions to the user you created in Main Step [3]
- ALTER ANY CONNECTION
- ALTER ANY CREDENTIAL
- ALTER ANY DATABASE
- CONNECT SQL(on by default)
- CREATE ANY DATABASE
- VIEW ANY DATABASE
- VIEW ANY DEFINITION(tables cannot be view within PSI:Capture without this option enabled)
- ALTER ANY CONNECTION
- Grant the Following permissions to the user you created in Main Step [3]
- Once done switch over to Service Manager, double check your formatting of connection string, and press Save.
- Capture Service will now create the Configuration and Batch Manager based on the Initial Catalog name.
For users that want more restricted permission sets and do not allow so much wide open access to the Master Database I set up another test. This test is the closest to minimal permissions I could come up with and allowed me to both Test Connection and Migrate from the Migration module "Database".
I have setup a new user within SQL Management Studio 2012.
The user is called PSIGENTEST.
Server roles are set to public only for the user PSIGENTEST.
In order for me to connect the group Public must have the following permissions; View any database, View any Definition, Connect SQL.
User mapping is set to the Database "Test Database"
User mapping for master is set to Public for the user PSIGENTEST.
PSIGENTEST only had the following permissions db_datawriter, db_datareader, and public for role membership for the database "Test Database".
The only granted explicit permission to "Test Database" for the user PSIGENTEST is "Connect SQL" granted by dbo.
The only granted explicit permission to the Master Database is set to "Connect SQL" granted by sa.
So if you right click on the user within Security in SSMS 2012 click properties, click on user mapping, map the user to the database of your choice, select db_datareader, db_datawriter, and public, right click on the master database to choose properties, select permissions, click on public server role(View any definition, View any Database, and Connect SQL granted by sa), click on the physical user and they should only have Connect SQL granted by sa,
With all that in place I was able to successfully test connection and migrate to the database mapped to the user.
Alert: This is for Local SQL Server and should only be used with SQL Server 2012 - 2019. If you are planning to deploy in Azure SQL Server we currently do not support this.
Comments
Please sign in to leave a comment.