Assess and upgrade to SQL Server 2025 with SSMS Migration Component
Upgrade to SQL Server 2025 with confidence. Starting with SSMS 22, the Hybrid & Migration component now includes upgrade assessment for SQL Server 2025. This feature enables you to quickly evaluate readiness for upgrade. SSMS also provides a streamlined migration path to the instance of higher version if in-place upgrade is not preferred.
Managed Identity on SQL Server On-Prem: The End of Stored Secrets
The Problem with Credentials in SQL Server For an On-Premises SQL Server to access Azure services, you traditionally need to store secrets: Common Scenarios Requiring Credentials Scenario Required Credential Backup to URL (Azure Blob) Storage account key or SAS token Extensible Key Management (Azure Key Vault) Service principal + secret Calling Azure OpenAI from T-SQL API key PolyBase to Azure Data Lake Service principal or key Associated Risks Manual Rotation Secrets expire. You need to plan and execute rotation and not forget to update all references. Secure Storage Where to store these secrets? In SQL Server via CREATE CREDENTIAL? In a config file? Each option has its risks. Attack Surface A compromised secret gives access to associated Azure resources. The more secrets you have, the larger the attack surface. Complex Auditing Who has access to these secrets? When were they used? Tracking is difficult. The Solution: Azure Arc + Managed Identity SQL Server 2025 connected to Azure Arc can geta Managed Identity : This identity: Is managed by Microsoft Entra ID Has no secret to store or rotate Can receive RBAC permissions on Azure resources Is centrally audited in Entra ID How It Works SQL Server 2025 On-Prem Azure Arc Agent installed on the server Managed Identity (automatically created in Entra ID) RBAC assignment on Azure resources -free access to Blob Storage, Key Vault, etc Step-by-Step Configuration Step 1: Enable Azure Arc on the Server and/or Register SQL Server in Azure Arc Follow the procedure describes in this article to onboard your server in Azure Arc. Connect Your SQL Server to Azure Arc Remember that you can also evaluate Azure Arc on a Azure VM (test use only) How to evaluate Azure Arc-enabled servers with an Azure virtual machine Step 2: Retrieve the Managed Identity The Managed Identity can be enabled and retrieved from Azure Arc | SQL Servers > “SQL Server instance” > Settings > Microsoft Entra ID Note: The Managed Identity is server-wide (not at the instance level) Step 3: Assign RBAC Roles Granting access to a Storage Account for backups $sqlServerId = (az resource show --resource-group "MyRG" --name "ServerName" --resource-type "Microsoft.HybridCompute/machines" --query identity.principalId -o tsv) az role assignment create --role "Storage Blob Data Contributor" ` --assignee-object-id $sqlServerId ` --scope "/subscriptions/xxx/resourceGroups/MyRG/providers/Microsoft.Storage/storageAccounts/mybackupaccount" Ex: Backup to URL Without Credential Before (with SAS token) -- Create a credential with a SAS token (expires, must be rotated) CREATE CREDENTIAL [https://mybackup.blob.core.windows.net/backups] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=2022-11-02&ss=b&srt=sco&sp=rwdlacup...' BACKUP DATABASE [MyDB] TO URL = 'https://mybackup.blob.core.windows.net/backups/MyDB.bak' WITH COMPRESSION After (with Managed Identity --No secret anymore CREATE CREDENTIAL [https://mybackup.blob.core.windows.net/backups] WITH IDENTITY = 'Managed Identity' BACKUP DATABASE [MyDB] TO URL = 'https://mybackup.blob.core.windows.net/backups/MyDB.bak' WITH COMPRESSION Extensible Key Management with Key Vault EKM Configuration with Managed Identity CREATE CREDENTIAL [MyAKV.vault.azure.net] WITH IDENTITY = 'Managed Identity' FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov; How Copilot Can Help Infrastructure Configuration Walk me through setting up Azure Arc for SQL Server 2025 to use Managed Identity for backups to Azure Blob Storage @mssql Generate the PowerShell commands to register my SQL Server with Azure Arc and configure RBAC for Key Vault access Identify Existing Credentials to Migrate List all credentials in my SQL Server that use SHARED ACCESS SIGNATURE or contain secrets, so I can plan migration to Managed Identity Migration Scripts I have backup jobs using SAS token credentials. Generate a migration script to convert them to use Managed Identity Troubleshooting My backup WITH MANAGED_IDENTITY fails with "Authorization failed". What are the steps to diagnose RBAC permission issues? @mssql The Azure Arc agent shows "Disconnected" status. How do I troubleshoot connectivity and re-register the server? Audit and Compliance Generate a report showing all Azure resources my SQL Server's Managed Identity has access to, with their RBAC role assignments Prerequisites and Limitations Prerequisites Azure Arc agent installed and connected SQL Server 2025, running on Windows Azure Extension for SQL Server. Current Limitations Failover cluster instances isn't supported. Disabling not recommended Only system-assigned managed identities are supported FIDO2 method not currently supported Azure public cloud access required Documentation Overview Managed identity overview Set Up Managed Identity and Microsoft Entra Authentication for SQL Server Enabled by Azure Arc Set up Transparent Data Encryption (TDE) Extensible Key Management with Azure Key VaultTLS 1.3 in SQL Server 2025: What It Really Changes for Security
Why TLS 1.3 matters TLS (Transport Layer Security) is the protocol that encrypts traffic between clients and servers. For many years, most SQL Server environments have relied on TLS 1.2, which dates back to 2008. TLS 1.3, finalized in 2018, is a significant evolution. It brings three main benefits: Better performance With fewer roundtrips and faster negotiation, secure connections are established much more quickly in practice Stronger security Old and weak algorithms are removed (RC4, SHA1, MD5, DES, 3DES…) Perfect Forward Secrecy (PFS) is mandatory. Even if a private key is compromised later, past sessions remain protected Simpler cipher suites: less configuration complexity, fewer chances to misconfigure Easier compliance Recommended by NIST Strongly encouraged by PCIDSS 4.0 Fewer exceptions to justify during audits What SQL Server 2025 adds SQL Server 2022 introduced TLS 1.3 through the new TDS 8.0 (Tabular Data Stream) protocol. And SQL Server 2025 makes it broadly usable across the SQL Server ecosystem, this is not limited to client connections only. Components that can use TLS 1.3 Client connections (ODBC, OLE DB, .NET) SQL Server Agent bcp utility sqlcmd utility Always On Availability Groups Always On Failover Cluster Instances Log Shipping Transactional replication Merge replication Peer-to-peer replication Snapshot replication Linked Servers PolyBase SQL Writer Service Prerequisites Valid certificates on all endpoints Selfsigned certificates are acceptable for testing In production, use certificates from a trusted CA or an internal PKI Updated drivers ODBC Driver 18+ for SQL Server OLE DB Driver 19+ for SQL Server Microsoft.Data.SqlClient 5.0+ Older drivers will either fall back to older TLS versions or fail to connect SQL Server configuration You can enforce encryption on all connections (Force Encryption) You can configure TDS 8.0 as the minimum protocol version (Force Strict Encryption) How Copilot can help I'm setting up log shipping between two SQL Server 2025 instances with TLS 1.3 enabled. What certificate requirements should I verify ? The MSSQL extension with GitHub Copilot can assist you when planning and rolling out TLS 1.3. Auditing the current state Analyze my current SQL Server encryption configuration. What TLS version are my connections using ? Are there any connections using deprecated protocols ? Copilot can generate and explain the queries that inspect the relevant DMVs and interpret the results. Generating configuration scripts Generate a script to configure SQL Server 2025 to require TLS 1.3 for all incoming connections, including the certificate configuration steps. Diagnosing compatibility issues My application fails to connect after enabling TDS 8.0. The error mentions "SSL Provider". What should I check and how do I fix it ? Reviewing linked servers Review my linked server configurations and identify which onesmight have issues with TLS 1.3 requirements. Documenting the changes Generate documentation for our security team explainingthe TLS configuration changes we're implementing for PCI-DSS compliance. It is possible that the AI generated content is incorrect. You remain responsible for reviewing, validating, and approving it before any use. Do not rely on this output without thorough human verification. Not intended for production use. Things to watch out for Linked Servers to legacy systems You may have linked servers that point to: Older SQL Server versions (2016 and earlier) ODBC sources that do not support TLS 1.2/1.3 Thirdparty servers with outdated TLS stacks If you enforce TLS 1.3 everywhere, these connections can fail. Possible approaches: Upgrade the remote systems Use temporary exceptions (not recommended longterm in production) Introduce a proxy or gateway that can handle protocol translation Replication to older versions Transactional or merge replication to subscribers running SQL Server 2019 or earlier may require mixed configurations. Legacy applications Applications using: .NET Framework with older System.Data.SqlClient versions Old ODBC/OLE DB drivers Thirdparty tools that are not updated may need to be upgraded or reconfigured before you enforce TLS 1.3. Suggested deployment strategy These steps are indicative; adapt them to your environment and change management process. Phase 1: Audit Inventory all inbound and outbound connections Identify client types and driver versions Document linked servers and replication paths Phase 2: Testing Enable TLS 1.3 in a staging or preproduction environment Test all critical applications and administrative tools Identify and resolve compatibility issues Phase 3: Progressive rollout Enable TLS 1.3 as an option without forcing it Monitor which connections still negotiate older TLS versions Once all key systems are compatible, enforce TLS 1.3 References TDS 8.0 - SQL Server Configure TLS 1.3 on SQL Server 2022 and later - SQL Server Encrypt Connections by Importing a Certificate - SQL Server Database Engine: Breaking Changes - SQL Server 2025 Certificate Requirements for SQL Server - SQL Server
