Microsoft SQL Server
Create an integration to manage access to a Microsoft SQL Server database
Microsoft SQL Server is a reliable and secure relational database management system. It can be used as the main data store for various applications, websites, and products.
Microsoft enables developers to create cloud-hosted SQL Server databases.
Through this integration, Apono helps you securely manage access to your Microsoft SQL Server database.
Prerequisites
Item | Description |
---|---|
Apono Connector | On-prem connection serving as a bridge between a Microsoft SQL Server database instance and Apono: |
Microsoft SQL Server Info | Information for the database instance to be integrated:
|
Create a Microsoft SQL Server user
You must create a user in your Microsoft SQL Server instance for the Apono connector.
Use the following steps to create a user and grant it permissions to your databases:
-
In your preferred client tool, create a new user. Be sure to set a strong password for the user.
The password must be a minimum of 8 characters and include characters from at least three of these four categories:
- Uppercase letters
- Lowercase letters
- Digits (0-9)
- Symbols
CREATE LOGIN apono_connector WITH PASSWORD = 'password';
-
Expose databases to the user. This allows Apono to view database names without accessing the contents of each database.
GRANT VIEW ANY DATABASE TO apono_connector;
- Grant the
ALTER ANY LOGIN
database permissions to the user in all the databases.USE master GRANT ALTER ANY LOGIN TO apono_connector;
-
Grant the user ADMIN permissions: Allows Apono to grant users administrative-level access, including the ability to execute and drop tables
USE master GRANT CONTROL SERVER TO apono_connector;
Grant and revoke server-level roles with Apono
To use Apono for MS SQL server-level roles, you’ll need to assign the Apono connector user the
securityadmin
role:USE master ALTER SERVER ROLE securityadmin ADD MEMBER server_principal
Members of the securityadmin fixed server role can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.
Read more here.
- Using the credentials from step 1, create a secret for the database instance:
You can now integrate Microsoft SQL Server.
Integrate Microsoft SQL Server
Follow these steps to complete the integration:
- On the Catalog tab, click Microsoft SQL Server. The Connect Microsoft SQL Server page appears.
- From the dropdown menu, select a connector.
If the desired connector is not listed, click + Add new connector and follow the instructions for creating a connector (AWS, Azure, GCP, Kubernetes).
- Under Select resource types, select one or multiple resource types for Apono to discover in all instances of the environment.
- Click Next. The Complete setup page appears.
- Enter a unique, alphanumeric, user-friendly Integration Name. This name is used to identify this integration when constructing an Access Flow.
- Enter the Hostname of the Microsoft SQL Server instance to connect.
- (Optional) Enter the Port value for the database. By default, Apono sets this value to 1433.
- (Optional) In the Credentials rotation period (in days) field, enter the number of days after which the database credentials must be rotated.
- Under Secret Store, associate the secret:
- Click Connect.
Now that you have completed this integration, you can create access flows that grant permission to your Microsoft SQL Server database.
Updated 5 days ago