As a CPS administrator, you will have access to the Admin Portal and the 'Databases' section under 'Infrastructure'.
Here is where you would start the process to add the database assuming you have the following pre-requisites handy.
1) Hostname of the database server
2) Database name
3) Database account name and password
4) Instance name containing the database
Obtaining these pre-requisites is where most people get tied up especially if they are not database administrators. But all of these can be obtained by requesting it from your DB Admin.
In the example below, you can see I completed the first page of the 'Add Database Wizard' with the minimum requirements listed above. Note that the Instance Name does not have a star (*) next to the field, however, most of the time this will fail if you don't specify the instance so I recommend adding this just to be safe.
The second page of the wizard will ask for a user that has access to this database. The account password can be managed by our CPS tenant, but in this example, we will leave it unmanaged and proceed to validate our settings.
That's what a successful process will look like after you enter all the necessary information. You should now see a newly added database with accounts that can be managed. Also, you can grant permissions to users to check out these accounts' passwords to access the database.
Here are some common issues that people experience when trying to add a database and how to resolve them.
I will not include common mistakes like entering the wrong name of the database or hostname or invalid password. That is still up to you to make sure you have the correct information with no typos.
This is one of the more common errors that can cause you a headache. Using the incorrect port (although it's not a requirement) will cause the wizard to fail. Most people like to use the default TCP port for SQL which is 1433.
When in reality this is asking you for the Instance TCP Dynamic Port which can be found in the SQL Server Configuration Manager (Steps on finding this at the end of the post).
In order to add a database, the account cannot be a domain account. It has to be a local SQL account that uses SQL Server authentication. You can add domain accounts to the database after it has been added but if you try this while adding the database you will get an error like the one below. More information on types of accounts can be found in our documentation.
Wrong Authentication Method:
Because the account you use must be a local SQL server account to add the database. The authentication method on your instance must be "Mixed Mode" or "SQL Server and Windows Authentication mode".
If this is set to only use "Windows Authentication mode" you will receive the same 'Verification failed' error shown above.
How do I obtain the TCP Dynamic Port for my instance?
This can be provided to you by your SQL admin, but for your own knowledge, this port number is obtained from the SQL Server Configuration Manager.
The screenshot below shows you where the port can be found, if you have multiple instances make sure you are checking the correct instance.
1) Open SQL Server Configuration Manager
2) Expand 'SQL Server Network Configuration'
3) Click on 'Protocols for '
4) Make sure TCP/IP is 'Enabled'
5) Right-click TCP/IP and click Properties
6) Under the tab 'IP Addresses' scroll down until you see 'IPAll'