Microsoft SQL Server

Connect Microsoft SQL Server to Oleria to see every login, user, role, and permission across your databases. Oleria also flags risky configurations, such as an enabled sa login, sysadmin members, and logins that can impersonate any other login.

This integration supports self-managed SQL Server (VM or on-premises), AWS RDS for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Google Cloud SQL for SQL Server. Pick the setup path that matches your deployment below.

What Oleria discovers

Area	Detail
Accounts	Every login and database user: SQL logins, Windows users, members of Windows group logins, Microsoft Entra users, certificate and asymmetric-key logins, contained users, users without a login, orphaned users, and application roles. A database user mapped to a login is the same account as that login in every database. SQL Server has no email field, so Oleria synthesizes one for identity matching.
Roles and groups	Server roles (fixed, `##MS_…##`, and user-defined) and database roles (fixed and user-defined), including nesting, plus `public` and `guest`. Windows and Microsoft Entra group logins appear as groups.
Resources	The instance, databases, schemas, tables, views, procedures, functions, sequences, synonyms, and linked servers. Columns, certificates, keys, assemblies, and types appear when a permission is granted on them.
Access grants	Explicit `GRANT` and `DENY` at server, database, schema, object, and column scope. Also implicit access: fixed-role permissions (for example, `db_datareader` grants `SELECT`), ownership, and the access that `sysadmin` and `CONTROL SERVER` holders have to everything. A `DENY` overrides a `GRANT`.
Impersonation	`IMPERSONATE` on a login or user, `IMPERSONATE ANY LOGIN`, `sysadmin`, `db_owner`, and `EXECUTE AS` modules. Database-level impersonation reaches server-level identity only in `TRUSTWORTHY` databases.
Risk signals	See Risk signals below.
Activity	SQL Server Audit events: sign-in success and failure, logout, password changes, account enable and disable, role membership changes, `GRANT`, `REVOKE`, and `DENY`, impersonation, schema changes, data access, and audit configuration changes. Requires SQL Server Audit configured for your deployment - see setup below.

Risk signals

Oleria reports these risks for SQL Server:

SQL Server sysadmin - fixed sysadmin or CONTROL SERVER, including members of a Windows group login.
sa login enabled
TRUSTWORTHY database owned by a sysadmin
Can impersonate any login
SQL login without password policy (CHECK_POLICY = OFF)
SQL login password never expires (CHECK_EXPIRATION = OFF)
SQL login password expired
Orphaned database user
guest enabled in a user database
Permission granted WITH GRANT OPTION - including members of a role that holds the permission.
Explicit grant to public - excluding SQL Server’s built-in defaults.

Prerequisites

A dedicated SQL Server login for Oleria, with the read permissions listed for your deployment below.
Network access from Oleria to your instance on the configured port (default 1433). Google Cloud SQL is the exception: Oleria connects through the Cloud SQL connector, so you don’t need to allowlist IP addresses.
Self-managed instances: SQL Server 2012 or later. SQL Server 2016 or later gives the most complete activity data.
Optional: additional permissions on that login, if you also want to run remediation actions. See Remediation actions.

SQL Server silently hides catalog rows that a login can’t see. A login without the read permissions below connects successfully but sees almost nothing. To avoid syncing a partial view, Oleria refuses the connection with an error that names the missing permission. Oleria re-checks permissions on every sync. If you later revoke them, the sync stops with an error instead of removing accounts and access from Oleria.

Set up the integration

Self-managed
AWS RDS
Azure SQL Database
Azure SQL Managed Instance
Google Cloud SQL

Create a connector login

Connect as an administrator and create a dedicated login for Oleria. Set CHECK_POLICY = ON so the login follows your password policy.

CREATE LOGIN oleria_reader WITH PASSWORD = '<strong password>', CHECK_POLICY = ON;
GRANT VIEW ANY DEFINITION TO oleria_reader;
GRANT CONNECT ANY DATABASE TO oleria_reader;
GRANT VIEW SERVER STATE TO oleria_reader;

VIEW ANY DEFINITION is required. It exposes all object and security metadata.
CONNECT ANY DATABASE is required. It is available on SQL Server 2014 and later. On older versions, create a user for the login in every database instead.
VIEW SERVER STATE is needed to read audit status for activity.

VIEW ANY SECURITY DEFINITION alone isn’t enough. It hides databases, schemas, and tables from the login.

Expand Active Directory groups (optional)

On a domain-joined instance, let Oleria expand the members of Windows group logins. Run this in the master database:

GRANT EXECUTE ON sys.xp_logininfo TO oleria_reader;

Without this grant, Windows group logins still appear, but Oleria doesn’t list their members.

Use Windows authentication (optional)

To connect with Windows authentication instead of a SQL login, create the login from a Windows account:

CREATE LOGIN [DOMAIN\svc_oleria] FROM WINDOWS;

Then grant it the same permissions as above. Oleria uses NT LAN Manager (NTLM) authentication with an explicit domain password. Kerberos-only setups (keytab) aren’t supported.

Enable SQL Server Audit for activity (optional)

Create and enable a server audit and a server audit specification. Follow Set up SQL Server Audit below. Oleria reads the files of the enabled audit automatically.

Grant permissions to the connector login

Connect as the RDS master user and run the grants from the Self-managed tab for a dedicated login. The RDS master user isn’t a sysadmin, but it can grant these permissions.

Find your instance endpoint

In the AWS console, open your RDS instance and copy the endpoint. You enter it as Host when you connect. This is the simplest path, and it needs no AWS permissions for Oleria.Alternatively, you can enter the AWS Region and RDS Instance Identifier and let Oleria look up the endpoint and port. This lookup calls rds:DescribeDBInstances in your AWS account, so it works only if your AWS account allows Oleria to make that call. Contact support@oleria.com to set up that access.

Oleria verifies TLS against the Amazon RDS certificate authority. The Trust Server Certificate option isn’t offered for RDS.

Enable SQL Server Audit for activity (optional)

In the AWS console, add the SQLSERVER_AUDIT option to an option group for your SQL Server edition and version, then attach that option group to your instance. Applying a new option group can require a reboot, depending on your instance settings.
Create the server audit and specification as described in Set up SQL Server Audit. Use D:\rdsdbdata\SQLAudit\ as the target path.

Oleria reads the audit files from the instance with the built-in RDS reader function, msdb.dbo.rds_fn_get_audit_file. The SQLSERVER_AUDIT option’s S3 bucket and IAM role settings are for copying audit files to S3. Oleria doesn’t use them, so you don’t need to configure them for Oleria.

Oleria excludes RDS’s own automation login (rdsa) from activity.

Create a connector login

Run this in the master database of the logical server. You can create a SQL login, or a login for a Microsoft Entra application with FROM EXTERNAL PROVIDER.

CREATE LOGIN oleria_reader WITH PASSWORD = '<strong password>';
ALTER SERVER ROLE ##MS_DefinitionReader## ADD MEMBER oleria_reader;
ALTER SERVER ROLE ##MS_DatabaseConnector## ADD MEMBER oleria_reader;
ALTER SERVER ROLE ##MS_LoginManager## ADD MEMBER oleria_reader;

For a Microsoft Entra application, use CREATE LOGIN [app-name] FROM EXTERNAL PROVIDER; instead of the first statement, then add it to the same three roles.

##MS_LoginManager## is required, because Azure SQL Database shows SQL logins only to login managers and to the server and Microsoft Entra admins. This role can also create and alter logins. Azure offers no read-only alternative. The server admin and the Microsoft Entra admin pass all checks without these roles.

Server role assignments can take up to 5 minutes to apply. If Oleria refuses the connection right after you grant the roles, wait and try again.

If you connect with a Microsoft Entra service principal, register an application in Microsoft Entra ID and create a client secret for it. Note the tenant ID, client ID, and client secret. Oleria always requires encryption and verifies certificates with Microsoft Entra authentication.

Enable auditing for activity (optional)

Choose one destination for Azure SQL auditing:

Log Analytics (recommended). Complete these steps:
1. Enable server or database auditing with a Log Analytics destination.
2. In the diagnostic setting, choose the Azure diagnostics destination table, not Resource specific. Oleria reads the SQLSecurityAuditEvents category of the AzureDiagnostics table.
3. Register a Microsoft Entra application and create a client secret. In the workspace, open Access control (IAM) and assign the application the Log Analytics Reader role.
4. Copy the Workspace ID from the workspace’s Overview page. You enter it as Log Analytics Workspace ID (optional) when you connect, along with the application’s tenant ID, client ID, and client secret.
Storage account. Enable auditing to a storage account and note the blob URL. You enter it as Audit File Path when you connect.

Azure SQL Database doesn’t support Windows authentication or xp_logininfo group expansion.

Create a connector login

Use the same login and grants as the Self-managed tab:

CREATE LOGIN oleria_reader WITH PASSWORD = '<strong password>', CHECK_POLICY = ON;
GRANT VIEW ANY DEFINITION TO oleria_reader;
GRANT CONNECT ANY DATABASE TO oleria_reader;
GRANT VIEW SERVER STATE TO oleria_reader;

To connect with a Microsoft Entra service principal, create the login with FROM EXTERNAL PROVIDER and grant it the same permissions.

Enable auditing for activity (optional)

Enable auditing with a Log Analytics destination, or with a storage account. Use the same options and requirements as the Azure SQL Database tab.

Choose a connector login

Connect as the built-in sqlserver user, or create a dedicated login and grant it the same permissions as the Self-managed tab. Only SQL Server authentication is available.

Note your Cloud SQL connection name

On the instance’s Overview page, copy the Connection name. It’s in project:region:instance format. Oleria connects through the Cloud SQL connector, so you don’t need to allowlist IP addresses.

Create a service account

In IAM & Admin, create a service account.
Grant it Cloud SQL Client (roles/cloudsql.client) on the project that contains the instance. Oleria’s Cloud SQL connector uses this role to authorize the connection. The service account needs no other role. Oleria reads audit data through the database connection, not through Google Cloud APIs.
Create a JSON key for the service account and download it. You paste its contents into the GCP Service Account Key (JSON, optional) field.

Enable SQL Server Audit for activity (optional)

Enable audit on the instance. Cloud SQL requires a Cloud Storage bucket for this, but Oleria doesn’t need access to it:

gcloud sql instances patch <INSTANCE> --audit-bucket-path=gs://<bucket>

As the sqlserver user, create the server audit and specification as described in Set up SQL Server Audit. Cloud SQL requires the path /var/opt/mssql/audit and a MAXSIZE between 2 and 50 MB.

Oleria reads the audit with the built-in Cloud SQL reader function. Until you enable audit, Oleria shows no activity and doesn’t report an error.

Oleria excludes Cloud SQL’s own management logins (CloudDbSql… connecting locally) from activity, though they appear as accounts. Cloud SQL Studio also connects as sqlserver. Oleria filters out its own sessions by application name.

Set up SQL Server Audit

Complete this section only if you want Oleria to show activity. Activity appears within about 5 minutes of the event. This section applies to self-managed SQL Server, AWS RDS, and Google Cloud SQL. Azure SQL Database and Azure SQL Managed Instance can’t write audits to a local folder. Use the Azure auditing options in their setup tabs instead.

Create the server audit

Create an audit that writes to a file. Use /var/opt/mssql/audit on Google Cloud SQL, D:\rdsdbdata\SQLAudit\ on AWS RDS, or any folder you choose on self-managed instances. Use these settings:

MAXSIZE = 50 MB
QUEUE_DELAY = 1000
ON_FAILURE = CONTINUE

Name the audit OleriaAudit.

Create the server audit specification

Create a server audit specification named OleriaAuditSpec for OleriaAudit. Add the action groups in this table.

Activity	Action groups
Sign-in	`SUCCESSFUL_LOGIN_GROUP`, `FAILED_LOGIN_GROUP`, `LOGOUT_GROUP`, `SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP`, `FAILED_DATABASE_AUTHENTICATION_GROUP`, `DATABASE_LOGOUT_GROUP`
Passwords	`LOGIN_CHANGE_PASSWORD_GROUP`, `USER_CHANGE_PASSWORD_GROUP`, `APPLICATION_ROLE_CHANGE_PASSWORD_GROUP`
Identities	`SERVER_PRINCIPAL_CHANGE_GROUP`, `DATABASE_PRINCIPAL_CHANGE_GROUP`
Role membership	`SERVER_ROLE_MEMBER_CHANGE_GROUP`, `DATABASE_ROLE_MEMBER_CHANGE_GROUP`
Permissions	`SERVER_PERMISSION_CHANGE_GROUP`, `SERVER_OBJECT_PERMISSION_CHANGE_GROUP`, `DATABASE_PERMISSION_CHANGE_GROUP`, `DATABASE_OBJECT_PERMISSION_CHANGE_GROUP`, `SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP`
Ownership	`SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP`, `DATABASE_OWNERSHIP_CHANGE_GROUP`, `DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP`, `SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP`
Impersonation	`SERVER_PRINCIPAL_IMPERSONATION_GROUP`, `DATABASE_PRINCIPAL_IMPERSONATION_GROUP`
Schema and configuration	`DATABASE_CHANGE_GROUP`, `DATABASE_OBJECT_CHANGE_GROUP`, `SCHEMA_OBJECT_CHANGE_GROUP`, `SERVER_OBJECT_CHANGE_GROUP`
Data access	`SCHEMA_OBJECT_ACCESS_GROUP`, `DATABASE_OBJECT_ACCESS_GROUP`
Operations	`SERVER_STATE_CHANGE_GROUP`, `SERVER_OPERATION_GROUP`, `DATABASE_OPERATION_GROUP`, `BACKUP_RESTORE_GROUP`, `DBCC_GROUP`
Tampering	`AUDIT_CHANGE_GROUP`, `TRACE_CHANGE_GROUP`
Application events	`USER_DEFINED_AUDIT_GROUP`

The data access groups generate a high volume of events. Omit them if you don’t need data access activity. BATCH_COMPLETED_GROUP (SQL Server 2022 and later) is also available, but it is very high volume.

Exclude the connector login (optional)

To reduce audit volume, exclude the Oleria login from the audit. Run this before you turn the audit on, because SQL Server doesn’t allow changing an enabled audit:

ALTER SERVER AUDIT OleriaAudit WHERE server_principal_name <> 'oleria_reader';

This isn’t required. Oleria already drops its own sessions when it reads the audit.

Turn on the audit

Enable the specification and the audit:

ALTER SERVER AUDIT SPECIFICATION OleriaAuditSpec WITH (STATE = ON);
ALTER SERVER AUDIT OleriaAudit WITH (STATE = ON);

Oleria reads the enabled audit automatically. You only need the Audit File Path (optional) field to override the location, or for Azure SQL Database file (blob) auditing.

Connect Microsoft SQL Server to Oleria

Open the integration

Complete the connection form

Select your Deployment first. The form updates to show only the fields that deployment needs.

Self-managed
AWS RDS
Azure SQL Database
Azure SQL Managed Instance
Google Cloud SQL

Field	Notes
Deployment	Select Self-managed (VM or on-premises).
Host	Required. The hostname or IP address of your SQL Server, reachable from Oleria.
Port	Defaults to `1433`.
Initial Database	Defaults to `master`.
Authentication	SQL Server authentication (default), Windows authentication (NTLM), or Microsoft Entra service principal (SQL Server 2022 with Microsoft Entra).
User	Required for SQL Server and Windows authentication. The login you created above. For Windows authentication, use `DOMAIN\user` or `user@domain`.
Password	Required for SQL Server and Windows authentication.
Entra Tenant ID, Entra Client ID, Entra Client Secret	Required for Microsoft Entra authentication.
Encryption	Required (default), Strict (TDS 8.0), Login only, or Disabled (not recommended). Microsoft Entra authentication requires encryption.
Trust Server Certificate	No (default) or Yes. Set to Yes only for self-signed certificates. It disables certificate verification. Not available with Microsoft Entra authentication.
Audit File Path (optional)	SQL Server Audit file pattern. Leave empty to use the instance’s enabled audit.

Field	Notes
Deployment	Select AWS RDS for SQL Server.
Host	The instance endpoint. This is the usual path. Required unless you enter AWS Region and RDS Instance Identifier.
AWS Region	Optional. An AWS region name, for example `us-east-1`. Use with RDS Instance Identifier instead of Host.
RDS Instance Identifier	Optional. Oleria looks up the endpoint from the region and identifier. Leave both empty if Oleria has no access to your AWS account, and enter the endpoint as Host.
Port	Defaults to `1433`.
Initial Database	Defaults to `master`.
Authentication	SQL Server authentication (default) or Windows authentication (NTLM) with AWS Managed Microsoft AD.
User	Required. The login you created above.
Password	Required.
Encryption	Required (default) or Strict (TDS 8.0).
Audit File Path (optional)	Leave empty to use the RDS default.

Field	Notes
Deployment	Select Azure SQL Database.
Azure SQL Server Name	Required. The logical server name. The host becomes `<name>.database.windows.net`.
Port	Defaults to `1433`.
Initial Database	Defaults to `master`.
Authentication	SQL Server authentication (default) or Microsoft Entra service principal.
User and Password	Required for SQL Server authentication.
Entra Tenant ID, Entra Client ID, Entra Client Secret	Required for Microsoft Entra authentication, and when you set a Log Analytics workspace.
Encryption	Required (default) or Strict (TDS 8.0). Azure requires encryption.
Log Analytics Workspace ID (optional)	Reads Azure SQL auditing from this workspace.
Audit File Path (optional)	Required for file (blob) auditing to a storage account. Enter the blob URL.

Field	Notes
Deployment	Select Azure SQL Managed Instance.
Host	Required. The hostname of your managed instance.
Port	Defaults to `1433`.
Initial Database	Defaults to `master`.
Authentication	SQL Server authentication (default) or Microsoft Entra service principal.
User and Password	Required for SQL Server authentication.
Entra Tenant ID, Entra Client ID, Entra Client Secret	Required for Microsoft Entra authentication, and when you set a Log Analytics workspace.
Encryption	Required (default) or Strict (TDS 8.0). Azure requires encryption.
Log Analytics Workspace ID (optional)	Reads Azure SQL auditing from this workspace.
Audit File Path (optional)	Blob URL for file auditing to a storage account.

Field	Notes
Deployment	Select Google Cloud SQL for SQL Server.
Cloud SQL Connection Name	Required. In `project:region:instance` format.
GCP Service Account Key (JSON, optional)	The key for the service account you created above. Provide it unless Oleria Support has told you otherwise.
Initial Database	Defaults to `master`.
Authentication	SQL Server authentication only.
User	Required. `sqlserver` or the login you created above.
Password	Required.
Audit File Path (optional)	Leave empty to use the Cloud SQL default.

Managed services and Microsoft Entra authentication reject the Login only and Disabled (not recommended) encryption options.

Complete the connection

Select Authenticate. Oleria validates the connection and its permissions, discovers your SQL Server accounts, resources, and access, and begins the first sync.

Verify the integration

Confirm Microsoft SQL Server appears on the Connected tab of your Oleria Integrations page. After the first sync completes, you can review the discovered accounts, roles, resources, access grants, and activity in your Oleria workspace. If the connection is refused with an error that names a missing permission, grant that permission to the connector login and try again.

Remediation actions

Oleria validates the connector login’s permissions before it runs each action. Disable SQL Server Login also requires approval in Oleria.

Action	What it does	Permission the connector login needs
Add Account to SQL Server Role	Adds a login to a server role, or the login’s database user to a database role. Reversible.	`ALTER` on the role. A fixed server role needs `sysadmin` or membership in that role. A fixed database role needs `db_owner`. `securityadmin` and `db_securityadmin` cover only user-defined roles.
Remove Account from SQL Server Role	Removes a login or database user from a role. Reversible.	The same as Add Account to SQL Server Role.
Disable SQL Server Login	Disables the login with `ALTER LOGIN … DISABLE`, then ends the login’s open sessions so access stops immediately. Reversible: Oleria can re-enable the login.	`ALTER ANY LOGIN` and `ALTER ANY CONNECTION`

Oleria never changes its own connector login or that login’s role memberships. Oleria also refuses a name that matches more than one principal, which can happen with case-sensitive collations.

Known limitations

No account creation or deletion: Oleria assigns and removes roles, and disables or re-enables logins. It can’t create or delete accounts.
Disable SQL Server Login scope: Disable SQL Server Login works only on server logins. It can’t disable contained database users. On Azure SQL Database, Oleria can’t end existing sessions, so they keep access until they disconnect. The action output says so.
Statement text is redacted: Oleria removes data from audited statements and keeps their shape, such as keywords and table and column names. Literal values become <redacted>, so personal data never reaches Oleria.
Synthetic email: SQL Server principals have no email field. Oleria synthesizes one, which may not match a real address. Windows and Microsoft Entra principals correlate through their directory identifiers, so they match your Active Directory and Microsoft Entra ID integrations.
Group membership: Oleria expands the members of Windows group logins only on domain-joined instances where the connector login can run xp_logininfo. Microsoft Entra group membership comes from the Microsoft Entra ID integration, not from SQL Server.
DENY evaluation: A member denied a permission doesn’t inherit it from a role on the same object. A DENY at a broader scope, such as a schema, appears as its own denial. Oleria doesn’t subtract it from a narrower grant, such as a table inside that schema.
Impersonation in non-TRUSTWORTHY databases: Oleria shows it only for database-scoped accounts, because SQL Server confines it to that database.
System and provider databases: master, msdb, model, tempdb, rdsadmin*, and gcloud_cloudsqladmin don’t appear as resources. SQL Agent jobs and proxies aren’t covered.
Offline or restoring databases: Oleria lists them as skipped, with their state, and doesn’t scan them.
Azure SQL Database login visibility: Seeing SQL logins requires ##MS_LoginManager##, which also allows login management. See the setup steps above.
Azure Log Analytics: Oleria reads only the AzureDiagnostics table. Auditing set to the Resource specific destination table isn’t supported.

Contact us

For questions about this integration, contact us at support@oleria.com.

Overview

Generic connectors

Identity providers

HR systems

Cloud infrastructure

Applications

Ticketing

Messaging

What Oleria discovers

Risk signals

Prerequisites

Set up the integration

Set up SQL Server Audit

Connect Microsoft SQL Server to Oleria

Verify the integration

Remediation actions

Known limitations

Contact us

​What Oleria discovers

​Risk signals

​Prerequisites

​Set up the integration

​Set up SQL Server Audit

​Connect Microsoft SQL Server to Oleria

​Verify the integration

​Remediation actions

​Known limitations

​Contact us

What Oleria discovers

Risk signals

Prerequisites

Set up the integration

Set up SQL Server Audit

Connect Microsoft SQL Server to Oleria

Verify the integration

Remediation actions

Known limitations

Contact us