Microsoft SQL Server
Microsoft SQL Server Integration with Secoda
Getting Started with Microsoft SQL Server
There are four high-level steps to start using Microsoft SQL Server with Secoda:
Set up environment
Create a database user
Whitelist the Secoda IP address
Integrate Microsoft SQL Server in Secoda
Supported Authentication Methods
Direct (SQL Authentication)
SQL Server username & password
Quick setup for any environment.
Azure AD (AAD Password)
Azure Active Directory account
Cloud or hybrid environments using centralized identity management.
Azure AD Service Principal
Azure Active Directory service principal
Similar benefits to Azure AD Password. Useful for tenants with enforced MFA.
Windows AD (NTLMv2)
Domain credentials via NTLMv2
On-premise or hybrid domains. Strongly recommended to pair with reverse/forward SSH tunnels to provide a layer of end-to-end encryption.
Set up Environment
If using Azure, you'll need to complete some setup depending on your authentication method:
Enable SQL authentication
Navigate to your SQL Server in the Azure portal.
Under Settings -> Microsoft Entra ID in the left menu, uncheck "Support only Microsoft Entra authentication for this server" and save your changes.
Register a service principal application.
In the Azure portal, open Microsoft Entra ID.
In the left menu, navigate to Manage -> App registrations.
Press "New registration", create a display name for your service principal, and press Register.
In your created application, note down the value of "Application (client) ID". Later, you will paste this into Secoda as the "client ID".
Navigate to Manage -> Certificates and secrets.
Press "New client secret" and fill out the description and expiry fields, then press "Add".
Note down the value of the client secret under "Value". Later, you will paste this into Secoda as the "client secret".
Create a Database User
For each database you wish to connect to Secoda, you will need a SQL user.
The username and password you originally set up for your cluster is your admin account. Keep this account for your own use. For Secoda (or any other third-party tool), create a separate, limited-scope user.
How to do this depends on your chosen authentication method:
-- Create a user named "secoda" that Secoda will use when connecting to your Microsoft SQL Server database.
CREATE USER secoda WITH PASSWORD = '<enter-strong-password-here>';
-- Grant read-only access on each database you would like Secoda to extract from.
GRANT SELECT ON DATABASE <yourdbname> TO secoda;Use these credentials (not your admin account) when configuring the integration in Secoda.
-- Replace <user> with the Azure AD user
CREATE USER <user> FROM EXTERNAL PROVIDER;
-- Grant read-only access on each database you would like Secoda to extract from.
GRANT SELECT ON DATABASE::<yourdbname> TO <user>;-- Replace <sp_name> with the display name of your service principal
CREATE USER <sp_name> FROM EXTERNAL PROVIDER;
-- Grant read-only access on each database you would like Secoda to extract from.
GRANT SELECT ON DATABASE::<yourdbname> TO <sp_name>;Connect Microsoft SQL Server to Secoda
In the Integrations tab of the Secoda app, click Add Integration.
Select Microsoft SQL Server.
Enter your connection details.
Choose an authentication method.
Click Connect.
Security
If using Azure SQL DB, you will need to allow Secoda through the Azure server-level firewall:
In the Azure portal, navigate to your SQL Server.
In the left menu, navigate to Security -> Networking.
Add a new firewall rule for Secoda. See below for IP addresses to whitelist.
If your SQL Server is inside a VPC or behind a firewall, whitelist Secoda’s outbound IP addresses so our workers can reach the host. Alternatively, use a reverse SSH Tunnel.
Once an SSH tunnel is configured (if you are using one), choose SSH Tunnel in the connection form and provide the tunnel details. See the full list here: What are the IP addresses for Secoda?
Troubleshooting
Timeout or “host unreachable”
The server is on a private network.
Whitelist the Secoda IPs or use a reverse SSH tunnel
Authentication failures
Wrong auth type selected.
Verify you picked the correct method (SQL Login, Azure AD, or Windows AD) and that the credentials are valid.
Permission errors
The secoda user lacks SELECT rights.
Re-run the GRANT SELECT statement for each required database.
Login is from an untrusted domain
Wrong auth type selected or incorrect username and password
Check that NTLMv2 is enabled on the SQL server and ensure username and password are correct
Login timeout expired (0) (SQLDriverConnect)
Connection failure while using Azure AD Service Principal
Ensure that the client ID is correct and the client secret is not expired.
Last updated
Was this helpful?