Using SQL Authentication with Credential Storage: Best Practices and Implementation 

Introduction 

Securing database access is a critical aspect of database management. While Windows Authentication is preferred for its security and ease of management, SQL Authentication is often necessary for applications that require cross-platform access or external integration. 

However, storing SQL credentials securely is crucial to prevent unauthorized access and potential security breaches. This blog will explore SQL Authentication, best practices for storing credentials securely, and a step-by-step guide on implementing credential storage securely. 

What is SQL Authentication? 

Using SQL Authentication with Credential Storage: Best Practices and Implementation 

1. Windows Authentication (Recommended): Uses Active Directory (AD) credentials. 

2. SQL Authentication: Requires a username and password managed by SQL Server. 

When to Use SQL Authentication? 

• When Active Directory integration is not possible. 

• For cross-platform applications (e.g., a Linux-based application connecting to SQL Server). 

• When third-party integrations need database access. 

• In cloud-based scenarios where applications connect to Azure SQL Database. 

Best Practices for Storing SQL Credentials Securely 

Since SQL Authentication involves storing a username and password, never hardcode credentials in your application or scripts. Instead, use secure credential storage mechanisms such as: 

 Azure Key Vault (For Azure SQL) 
Windows Credential Manager (For on-premises SQL) 
Environment Variables (For temporary storage, but with caution) 
Encrypted Configuration Files 
Secure Password Vaults 

Securely Storing SQL Credentials Using Azure Key Vault 

For cloud-based applications using Azure SQL Database, storing credentials in Azure Key Vault is the best approach. 

Step 1: Create an Azure Key Vault 

1. Go to Azure Portal → Search for Key Vault → Click Create. 

2. Set a Name, Subscription, and Resource Group. 

3. Choose the Standard Pricing Tier. 

Step 2: Add SQL Credentials to Azure Key Vault 

1. Navigate to Azure Key Vault → Secrets → Click Generate/Import. 

2. Set Secret Name: SQLServer-Password. 

3. Enter the SQL password as the Secret Value. 

4. Click Create. 

Step 3: Retrieve Credentials Securely in an Application 

To connect securely from a Python application, use Azure Identity SDK: 

from azure.identity import DefaultAzureCredential 

from azure.keyvault.secrets import SecretClient 

import pyodbc 

# Set Key Vault details 

key_vault_name = “my-keyvault” 

secret_name = “SQLServer-Password” 

KV_URI = f”https://{key_vault_name}.vault.azure.net” 

# Authenticate with Azure Key Vault 

credential = DefaultAzureCredential() 

client = SecretClient(vault_url=KV_URI, credential=credential) 

# Retrieve SQL Password securely 

retrieved_secret = client.get_secret(secret_name) 

sql_password = retrieved_secret.value 

# Connect to Azure SQL Database securely 

conn = pyodbc.connect(f’DRIVER={{ODBC Driver 17 for SQL Server}};’ 

                      f’SERVER=myserver.database.windows.net;’ 

                      f’DATABASE=mydb;’ 

                      f’UID=sqladmin;’ 

                      f’PWD={sql_password}’) 

print(“Connected securely!”) 

Advantages: 

• No hardcoded credentials in the code. 

• Passwords stored securely in Azure Key Vault. 

•  Works with Managed Identity for automatic authentication. 

Securely Storing SQL Credentials in Windows Credential Manager 

For on-premises SQL Server, Windows Credential Manager can store SQL credentials securely. 

Step 1: Store Credentials in Credential Manager 

1. Open Credential Manager in Windows. 

2. Click Add a generic credential. 

3. Enter the SQL Server name as the Internet or Network Address. 

4. Enter the SQL Username and Password. 

5. Click OK. 

Step 2: Retrieve Credentials in a PowerShell Script 

To use stored credentials securely in a PowerShell script: 

$cred = Get-StoredCredential -Target “SQLServerCredentials” 

$sqlUsername = $cred.UserName 

$sqlPassword = $cred.Password 

$connectionString = “Server=MyServer;Database=MyDB;User Id=$sqlUsername;Password=$sqlPassword;” 

$connection = New-Object System.Data.SqlClient.SqlConnection 

$connection.ConnectionString = $connectionString 

$connection.Open() 

Write-Host “Connected securely!” 

Advantages: 

• No plaintext credentials in scripts. 

• Easier access control with Windows security policies. 

Alternative Approaches for Secure SQL Credential Storage 

1. Using Encrypted Configuration Files 

You can store SQL credentials in an encrypted configuration file instead of plaintext. 

Example: Encrypting credentials in appsettings.json for a .NET application. 

{ 

  “ConnectionStrings”: { 

    “DefaultConnection”: “Server=myserver;Database=mydb;User Id=sqladmin;Password=EncryptedPassword;” 

  } 

} 

Then, use ASP.NET Data Protection API to decrypt the credentials. 

2. Using Environment Variables 

For temporary storage, you can store credentials in environment variables. 

Set environment variables: 

export SQL_USER=”sqladmin” 

export SQL_PASS=”mypassword” 

Retrieve credentials in Python: 

import os 

sql_user = os.getenv(“SQL_USER”) 

sql_pass = os.getenv(“SQL_PASS”) 

Caution: 

• Environment variables are temporary and volatile. 

• They should not be used for long-term storage of sensitive credentials. 

Conclusion 

Using SQL Authentication requires careful handling of credentials to avoid security risks. 

Best practices for secure credential storage include: 
✔ Using Azure Key Vault for cloud-based authentication. 
✔ Leveraging Windows Credential Manager for on-prem SQL authentication. 
✔ Encrypting configuration files instead of hardcoding passwords. 
✔ Using environment variables cautiously for temporary storage. 

By implementing these security measures, you can ensure secure database access while preventing unauthorized access to sensitive data.  

Afroz Labbai  

Data Engineering  

Addend Analytics  

Addend Analytics is a leading Power BI consulting services provider and Microsoft Power BI partners based in Mumbai, India. In addition to Power BI implementations, we specialize in providing end-to-end solutions like Business Central with Power BI to unlock actionable insights. Our expertise also extends to Microsoft Fabric consulting, offering competitive Microsoft Fabric pricing to meet your business needs. 

We have successfully delivered Power BI for Manufacturing industry, with real-time Power BI manufacturing dashboards. Having successfully completed over 100 projects across industries such as financial services, banking, insurance, retail, sales, real estate, logistics, and healthcare. Whether you’re exploring Business Central implementation cost or seeking advanced data analytics, Addend Analytics is here to help.Get a free consultation now by emailing us at kamal.sharma@addendanalytics.com.