# Microsoft SQL Server ## Overview The Veza integration for Microsoft SQL Server enables discovery of users, roles, databases, and permissions from both standalone SQL Server instances and Azure SQL deployments. The integration supports SQL Server 2008 and later, Azure SQL Database, and Azure SQL Managed Instance. Veza connects via a local SQL Server login using the configured host, port, username, and password. The integration also supports SQL Server Named Instances, which use dynamic ports assigned at startup. For secure connectivity, an [Insight Point](/4yItIzMvkpAvMVFAamTf/integrations/connectivity/insight-point.md) is recommended, particularly for on-premises deployments. The integration automatically detects whether it's connecting to standalone SQL Server or Azure SQL and adjusts entity discovery accordingly. **Note:** System databases (master, model, msdb, tempdb) are excluded from discovery by default. ## Configuring SQL Server ### Create SQL Database User **1.** Connect to your SQL Server and create new SQL Server Login with password. To enable discovery for SQL Server, you will need credentials for a local SQL Server user (Login with password). Connect to the server using your client of choice, and create the user with the command: ```sql CREATE LOGIN WITH PASSWORD = ''; ``` **2.** Assign read only permissions for the service account. Grant the following permissions to the SQL Server Login from the previous step: #### SQL 2008 and 2012 ```sql GRANT VIEW ANY DEFINITION TO ; GRANT VIEW ANY DATABASE TO ; GRANT CONNECT SQL TO ; ``` #### SQL 2014 and later ```sql GRANT VIEW ANY DEFINITION TO ; GRANT CONNECT ANY DATABASE TO ; ``` ### Configure Veza To add the connection, go to Veza **Integrations** > **Create Integration** and choose "SQL Server". 1. If you are using an external **Insight Point** for discovery, change the selection from the default internal Insight Point to the one you deployed. 2. Enter the server **Host**, **Login user**, and **Password**. 3. Configure the connection method based on your SQL Server deployment: * **Static port**: Enter the **Port** number (e.g., `1433` for the default SQL Server port). Leave **Instance Name** empty. * **Named Instance**: Enter the **Instance Name** and leave **Port** empty. Use this option when connecting to SQL Server Named Instances that use dynamic ports.

Do not specify both Port and Instance Name. Named Instances use dynamic ports, so specifying a port will cause a connection error.

4. Optionally, prevent discovery of some databases and schemas by adding names to the allow and deny lists: * **Database Allow/Deny List**: Enter database names (e.g., `SalesDB`, `Reporting`). If the allow list is populated, only listed databases are discovered. If the deny list is populated, those databases are skipped. * **Schema Allow/Deny List**: Enter schema names (e.g., `dbo`, `sales`). Use this to limit which schemas are extracted within discovered databases. * Both lists support wildcards: `*` matches any number of characters (e.g., `test_*` excludes all databases prefixed with `test_`). 5. Click **Save** to enable the integration and queue parsing. The SQL Server will be registered and appear under "Discovered Data Sources" on the **Configuration** > **All Data Sources** tab. {% hint style="info" %} System databases are not included in the database fetching by default. Enable **Gather system databases** in the integration configuration to include them. {% endhint %}
About SQL Server Named Instances SQL Server supports running multiple instances on a single host using Named Instances. Each named instance operates independently and is assigned a dynamic port at startup by the SQL Server Browser service. When connecting to a Named Instance: * The connection uses the format `hostname\instancename` internally * The SQL Server Browser service (UDP port 1434) resolves the instance name to its current port * Ensure the SQL Server Browser service is running and accessible from the Insight Point For more information, see [Microsoft's documentation on SQL Server instance configuration](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port).
## Supported Entities The Veza integration for Microsoft SQL Server discovers the following entities and attributes: {% hint style="info" %} Many entities include additional attributes populated by Veza's enrichment system (such as owners, identity classifications, and risk scores). See [Enrichment Configuration](/4yItIzMvkpAvMVFAamTf/integrations/configuration/enrichment.md) for details on configuring these attributes. {% endhint %} ### Identity Entities #### SQL Server Login Server-level login that can connect to the SQL Server instance. Logins can be Windows-authenticated or SQL Server-authenticated and have permissions at the server level. **Attributes:** * `full_admin` * `identity_unique_id` * `is_active` * `is_disabled` * `principal_type` * `server_type` * `sid` * `user_type` #### SQL Server Database User Database-level user mapped to a server login or contained within a database. Users are granted permissions within specific databases and can be members of database roles. **Attributes:** * `identity_unique_id` * `is_active` * `principal_type` * `server_type` * `sid` * `user_type` ### Role Entities #### SQL Server Role Server-level role that groups permissions at the instance level. Can be fixed server roles (e.g., sysadmin, securityadmin) or user-defined roles. **Attributes:** * `identity_unique_id` * `is_disabled` * `is_fixed_role` * `principal_type` * `server_type` * `sid` * `user_type` **Notes:** * Fixed server roles include: sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator, bulkadmin, public * User-defined roles provide custom permission groupings #### SQL Server Database Role Database-level role that groups permissions within a specific database. Can be fixed database roles (e.g., db\_owner, db\_datareader) or user-defined roles. **Attributes:** * `identity_unique_id` * `is_fixed_role` * `principal_type` * `server_type` * `sid` * `user_type` **Notes:** * Fixed database roles include: db\_owner, db\_securityadmin, db\_accessadmin, db\_backupoperator, db\_ddladmin, db\_datawriter, db\_datareader, db\_denydatawriter, db\_denydatareader * User-defined database roles provide custom permission groupings within databases ### Container Entities #### SQL Server Service Top-level container representing a SQL Server service. Supports both Azure SQL and standalone SQL Server deployments. **Attributes:** * `azure_id` - Azure resource ID (for Azure SQL) * `azure_tenant_id` - Azure tenant ID (for Azure SQL) * `server_type` - Server type: `azure_sql` or `standalone_sql` #### SQL Server Instance SQL Server instance that can contain multiple databases. Manages server-level logins, roles, and configurations. **Attributes:** * `azure_id` - Azure resource ID (for Azure SQL Managed Instance) * `builtin_permissions` - JSON string defining built-in permission structures * `domain_name` - Windows domain name for domain-joined instances * `server_type` - Server type: `azure_sql` or `standalone_sql` * `version` - SQL Server version (e.g., "15.0.2000.5" for SQL Server 2019) #### SQL Server Database Database within a SQL Server instance containing schemas, tables, views, and database users. **Attributes:** * `azure_id` * `encryption_enabled` * `encryption_encryptor_type` * `encryption_key` * `encryption_state` * `external_datasource_id` * `instance` * `server_type` **Notes:** * System databases (master, model, msdb, tempdb) are excluded from discovery * TDE encryption status is tracked for compliance and security monitoring ### Data Object Entities #### SQL Server Schema Database schema (namespace) containing tables, views, stored procedures, and other database objects. **Attributes:** * `database_name` * `server_type` #### SQL Server Table Database table containing structured data with rows and columns. **Attributes:** * `created_at` * `database_name` * `schema_name` * `server_type` * `updated_at` #### SQL Server View Database view - a virtual table based on a SQL query result set. Views can simplify complex queries and provide security by restricting access to specific columns. **Attributes:** * `created_at` * `database_name` * `schema_name` * `server_type` * `updated_at` ### Permission Entities #### SQL Server Effective Permission Server-level effective permissions showing cumulative access rights at the instance level, calculated from direct grants, role memberships, and inherited permissions. **Attributes:** * `permissions` - Array of server-level permissions * `sub_permissions` - Array of inherited sub-permissions * `data_read_privilege` - Boolean for direct data read access * `data_write_privilege` - Boolean for direct data write access * `data_create_privilege` - Boolean for direct data creation access * `data_delete_privilege` - Boolean for direct data delete access * `metadata_read_privilege` - Boolean for direct metadata read access * `metadata_write_privilege` - Boolean for direct metadata write access * `metadata_create_privilege` - Boolean for direct metadata creation access * `metadata_delete_privilege` - Boolean for direct metadata delete access * `non_data_privilege` - Boolean for non-data privileges (e.g., administrative) * `uncategorized_privilege` - Boolean for uncategorized permissions * `sub_data_read_privilege` - Boolean for inherited data read access * `sub_data_write_privilege` - Boolean for inherited data write access * `sub_data_create_privilege` - Boolean for inherited data creation access * `sub_data_delete_privilege` - Boolean for inherited data delete access * `sub_metadata_read_privilege` - Boolean for inherited metadata read access * `sub_metadata_write_privilege` - Boolean for inherited metadata write access * `sub_metadata_create_privilege` - Boolean for inherited metadata creation access * `sub_metadata_delete_privilege` - Boolean for inherited metadata delete access * `sub_non_data_privilege` - Boolean for inherited non-data privileges * `sub_uncategorized_privilege` - Boolean for inherited uncategorized permissions * `server_type` - Server type: `azure_sql` or `standalone_sql` #### SQL Server Database Effective Permission Database-level effective permissions showing cumulative access rights within a specific database, calculated from direct grants, role memberships, and inherited permissions. **Attributes:** * Inherits all attributes from SQL Server Effective Permission (see above) * Scoped to a specific database rather than server-level ## Permissions and Effective Access SQL Server uses a hierarchical permission model with permissions granted at multiple levels. Veza calculates **Effective Permissions** showing the cumulative access granted through: * Direct permission grants to users or logins * Server role memberships * Database role memberships * Schema ownership * Database ownership * Object ownership ### Permission Categorization Veza categorizes SQL Server permissions into: | Category | Description | Examples | | ------------------- | ------------------------------------- | ------------------------------- | | **Data Read** | Query and view data | SELECT on tables/views | | **Data Write** | Modify existing data | UPDATE on tables | | **Data Create** | Insert new data | INSERT on tables | | **Data Delete** | Remove data | DELETE on tables | | **Metadata Read** | View object definitions | VIEW DEFINITION | | **Metadata Write** | Modify object definitions | ALTER on objects | | **Metadata Create** | Create new objects | CREATE TABLE, CREATE VIEW | | **Metadata Delete** | Drop objects | DROP on objects | | **Non-Data** | Administrative and control operations | CONTROL SERVER, BACKUP DATABASE | | **Uncategorized** | Permissions not in above categories | Various specialized permissions | ### Common SQL Server Permissions #### Server-Level Permissions | Permission | Description | | -------------------- | ------------------------------------- | | `CONTROL SERVER` | Full control over the server instance | | `ALTER ANY LOGIN` | Create, modify, or drop logins | | `ALTER ANY DATABASE` | Create, modify, or drop databases | | `VIEW ANY DATABASE` | View all databases | | `VIEW SERVER STATE` | View server dynamic management views | #### Database-Level Permissions | Permission | Description | | ------------------ | -------------------------------------- | | `CONTROL` | Full control over the database | | `ALTER` | Modify database properties | | `ALTER ANY USER` | Create, modify, or drop database users | | `ALTER ANY ROLE` | Create, modify, or drop database roles | | `ALTER ANY SCHEMA` | Create, modify, or drop schemas | | `CREATE TABLE` | Create new tables | | `CREATE VIEW` | Create new views | | `BACKUP DATABASE` | Perform database backups | #### Object-Level Permissions | Permission | Description | | ---------- | ---------------------------------- | | `SELECT` | Query data from tables or views | | `INSERT` | Add new rows to tables | | `UPDATE` | Modify existing data in tables | | `DELETE` | Remove rows from tables | | `EXECUTE` | Run stored procedures or functions | | `ALTER` | Modify object definitions | | `CONTROL` | Full control over the object | --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.veza.com/4yItIzMvkpAvMVFAamTf/integrations/integrations/sql-server.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.