Skip to main content

Setting up the SQL DB

Setting up the Log and Backend databases on SQL DB comprises of the following actions:

Connect to Azure SQL Server​

  1. Launch Microsoft SQL Server Management Studio (SSMS).

  2. In the Object Explorer, click Connect → Database Engine.

  3. The Connect to Server window opens.

  4. Fill in the connection details:

    1. Server type: Database Engine
    2. Server name:

Code

<server-name>.database.windows.net,3342

(Example: her-sql.public.dededc70fdf0.database.windows.net,3342) 3. Authentication: Microsoft Entra MFA (or Azure AD authentication as per your setup) 4. User name: sqladmin (or your Azure SQL admin user) 5. Under Connection Security:

  1. Encryption: Mandatory
  2. Leave Trust server certificate unchecked (recommended)
  3. Click Connect.
  4. Complete the MFA login if prompted.

Verify the connection​

  1. After successful login, the Object Explorer refreshes.

  2. Expand the connected server node.

  3. Expand Databases.

  4. You should see:

    1. System DatabasesThis confirms that the connection to Azure SQL Server is successful.

Create Log and Backend DB Schemas and assign privileges​

  1. In Object Explorer, right-click on the server or a specific database (e.g., master).
  2. Click New Query.
  3. A new SQL editor window opens.
  4. Ensure the correct database is selected in the dropdown (top-left of query window), e.g.:

Code

master

  1. The status bar at the bottom should show:

    1. Connected
    2. Server name
    3. Logged-in user

  2. In the query editor, run the SQL commands as shown in the following example:

Code

create user rancherbackend identified by "Rancher@123";

ALTER USER rancherbackend quota unlimited on USERS;

GRANT CREATE SESSION TO rancherbackend;
GRANT CREATE TABLE TO rancherbackend;
GRANT CREATE ANY TABLE TO rancherbackend;
GRANT ALTER ANY TABLE TO rancherbackend;
GRANT DROP ANY TABLE TO rancherbackend;
GRANT DELETE ANY TABLE TO rancherbackend;
GRANT SELECT ANY TABLE TO rancherbackend;
GRANT UPDATE ANY TABLE TO rancherbackend;
GRANT INSERT ANY TABLE TO rancherbackend;
GRANT CREATE ANY INDEX TO rancherbackend;
GRANT ALTER ANY INDEX TO rancherbackend;
GRANT DROP ANY INDEX TO rancherbackend;
GRANT CREATE PROCEDURE TO rancherbackend;
GRANT CREATE ANY PROCEDURE TO rancherbackend;
GRANT ALTER ANY PROCEDURE TO rancherbackend;
GRANT DROP ANY PROCEDURE TO rancherbackend;
GRANT EXECUTE ANY PROCEDURE TO rancherbackend;
GRANT CREATE VIEW TO rancherbackend;
GRANT CREATE ANY VIEW TO rancherbackend;

In the SQL commands above: rancherbackend is the Backend DB username  Rancher@123 is the Backend DB password Backend DB Schema name is derived from the Backend DB username, which is rancherbackend. 7. Click Execute or press F5. 8. Repeat the steps 6 and 7 for log database. 9. Check the Messages tab for success or errors. 10. If successful, the changes are applied to the Azure SQL Database.

Best practices to follow​

  • Always verify:

    • Correct server name and port
    • Correct authentication method
    • Correct target database

  • Use least-privilege permissions when granting access.

  • For Azure SQL, prefer Azure AD users where possible instead of SQL logins.