How to Check DB Account Logins in Azure Sql Server

How to Check DB Account Logins in Azure Sql Server

DB Accounts refers to accounts like:

• sysadmin - full access
• reader_accounts - with read permissions only
• service_accounts - with read permissions and write permissions to certain tables only

These accounts are used as connection strings for services to connect to the database and are stored in KeyVault.

Each account has READ access to every other table in the database but it has limited WRITE and UPDATE access to its own specified tables.

We want to know which pods are using sysadmin as the fall back connection string. If there are logs for account logins with IP addresses, we can then confirm the usage of this sysadmin account.

Since we have previously turned on Server Side Audits on Azure, we have already defined the storage and log analytics location.

Go to the log analytics namespace defined and run a KQL query to check the logs:

KQL Query:

AzureDiagnostics
| where database_name_s has "name-of-database-server" and server_principal_name_s has "sysadmin"
| summarize count() by server_principal_name_s, client_ip_s, host_name_s

If there is a need to check the ip addresses of the pods, you can also add -o wide to the usual get pods (assuming you are using k8):

kubectl get pods -n <defined_namespace> -o wide