SQL Server - Always On High Availability Group Setup
We have an Always On High Availability Group setup with three database servers — two on the same subnet and one on a different subnet. The application server interacts with these database servers through the AG listener. Currently, this setup is running with application version X, and we have received a new application version Y for upgrade/migration (which includes database changes). To perform the application update, please clarify the recommended approach: Should I remove the database from the Availability Group and point to the primary replica to continue with the application update? or Can I proceed with the application update while the Always On configuration remains active, pointing to the AG listener?
Ollama on HTTPS for SQL Server
Here is a quick procedure to deploy an Ubuntu container with Ollama and expose its API over HTTPS. The goal is to allow a fast deployment, even for those unfamiliar with Docker or Language Models, making it easy to set up an offline platform for generating embeddings and using Small Language Models This is particularly useful when testing SQL Server 2025 for fully on-premises environment use cases, since SQL Server only allows access to HTTPS endpoints. However, HTTP remains open for testing purposes. Please note that this example is CPU-based, as deploying with (integrated) GPU support involves additional, less straightforward steps. This example is provided solely to illustrate the concept, is not intended for production use, and comes without any guarantee of performance or security. Prerequisites To continue, you need to have Docker Desktop, WSL and SQL Server 2025 (currently Release Candidate 1) Docker Desktop Install WSL | Microsoft Learn SQL Server 2025 Preview | Microsoft Evaluation Center Create a Dockerfile First, create a working directory. In this example, C:\Docker\Ollama will be used. Simply create a file named Dockerfile (without an extension) and paste the following content into it. FROM ubuntu:25.10 RUN apt update && apt install -y curl gnupg2 ca-certificates lsb-release apt-transport-https software-properties-common unzip nano openssl net-tools RUN curl -fsSL https://ollama.com/install.sh | bash RUN curl -1sLf 'https://dl.cloudsmith.io/public/caddy/stable/gpg.key' | gpg --dearmor -o /usr/share/keyrings/caddy-stable-archive-keyring.gpg RUN curl -1sLf 'https://dl.cloudsmith.io/public/caddy/stable/debian.deb.txt' | tee /etc/apt/sources.list.d/caddy-stable.list RUN apt update && apt install -y caddy RUN mkdir -p /etc/caddy/certs RUN cat > /etc/caddy/certs/san.cnf <<EOF [req] default_bits = 2048 prompt = no default_md = sha256 req_extensions = req_ext distinguished_name = dn [dn] CN = 127.0.0.1 [req_ext] subjectAltName = @alt_names [alt_names] IP.1 = 127.0.0.1 DNS.1 = localhost EOF RUN openssl req -x509 -nodes -days 365 -newkey rsa:2048 -keyout /etc/caddy/certs/localhost.key -out /etc/caddy/certs/localhost.crt -config /etc/caddy/certs/san.cnf -extensions req_ext RUN echo "https://:443 {\n tls /etc/caddy/certs/localhost.crt /etc/caddy/certs/localhost.key\n reverse_proxy localhost:11434\n}" >> /etc/caddy/Caddyfile RUN echo "#!/bin/bash" > /usr/local/bin/entrypoint.sh && \ echo "set -e" >> /usr/local/bin/entrypoint.sh && \ echo "OLLAMA_HOST=0.0.0.0 ollama serve >> /var/log/ollama.log 2>&1 &" >> /usr/local/bin/entrypoint.sh && \ echo "caddy run --config /etc/caddy/Caddyfile --adapter caddyfile >> /var/log/caddy.log 2>&1 &" >> /usr/local/bin/entrypoint.sh && \ echo "tail -f /var/log/ollama.log /var/log/caddy.log" >> /usr/local/bin/entrypoint.sh && \ chmod 755 /usr/local/bin/entrypoint.sh ENTRYPOINT ["/usr/local/bin/entrypoint.sh"] For your information, this file allows the creation of an image based on Ubuntu 25.10 and includes: Ollama, for running the models Caddy, for the reverse proxy Creation of a certificate for the HTTPS endpoint on localhost Create the container After opening a Powershell terminal, execute the following commands: cd C:\Docker\Ollama #Build the image from the Dockerfile. docker build -t ollama-https . #Create a container based on the image ollama-https docker run --name ollama-https -d -it -p 443:443 -p 11434:11434 ollama-https #Copy the certificate created into the current Windows directory docker cp ollama-https:/etc/caddy/certs/localhost.crt . # Install the certificate in Trusted Root Certification Authorities Import-Certificate -FilePath "localhost.crt" -CertStoreLocation "Cert:\LocalMachine\Root" #Check Https (wget https://localhost).Content #Check Http (wget http://localhost:11434).Content Ollama is now running With a browser, connect to https://localhost Retrieve Models No model is retrieved when the image is created, as this depends on each use case, and for some models, the size can be substantial. Here’s a quick example for pulling an embedding model, Nomic, and a small language model, Phi3. Ollama Search docker exec ollama-https ollama pull nomic-embed-text docker exec ollama-https ollama pull phi3:mini A quick example with SQL Server 2025 A quick demonstration using the WideWorldImporters database (Wide World Importers sample database) use [master] GO ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 170 WITH ROLLBACK IMMEDIATE GO DBCC TRACEON(466, 474, 13981, -1) GO Note: With RC1, you can use the PREVIEW_FEATURES database-scoped configuration T-SQL Declare an external model for embeddings. use [WideWorldImporters] GO CREATE EXTERNAL MODEL NomicLocal AUTHORIZATION dbo WITH ( LOCATION = 'https://localhost/api/embed', API_FORMAT = 'ollama', MODEL_TYPE = EMBEDDINGS, MODEL = 'nomic-embed-text' ) to enable semantic search capabilities on StockItems, we will create a dedicated table to store embeddings (no chunking in this example) along with a vector index optimized for cosine similarity use [WideWorldImporters] GO CREATE TABLE [Warehouse].[StockItemsEmbedding](StockItemEmbeddingID int identity (1,1) PRIMARY KEY, StockItemId int, SearchDetails nvarchar(max), Embedding vector(768)) GO INSERT INTO [Warehouse].[StockItemsEmbedding] SELECT si.StockItemID, si.SearchDetails, AI_GENERATE_EMBEDDINGS(si.SearchDetails USE MODEL NomicLocal) /* Generate embeddings from declared external model */ FROM [Warehouse].[StockItems] si GO /* Check */ SELECT * FROM [Warehouse].[StockItemsEmbedding] GO CREATE VECTOR INDEX IXV_1 ON [Warehouse].[StockItemsEmbedding] (Embedding) WITH (METRIC = 'cosine', TYPE = 'DiskANN') GO /* User Input */ DECLARE @UserInput varchar(max) = 'Which product is best suited for shipping small items?' /* and Generate embeddings for user input */ DECLARE @UserInputV vector(768) = AI_GENERATE_EMBEDDINGS(@UserInput USE MODEL NomicLocal) DECLARE @ModelInput nvarchar(max) DECLARE AS NVARCHAR (MAX) DECLARE nvarchar(max) /* Similarity Search on StockItems and Model Input creation*/ SELECT @ModelInput = STRING_AGG('ProductDetails: ' + sie.SearchDetails + 'UnitPrice: ' + CAST(si.UnitPrice AS nvarchar(max)), ' \n\n') FROM VECTOR_SEARCH( TABLE = [Warehouse].[StockItemsEmbedding] as sie, COLUMN = Embedding, SIMILAR_TO = @UserInputV, METRIC = 'cosine', TOP_N = 10 ) JOIN [Warehouse].[StockItems] si ON si.StockItemId = sie.StockItemId /* Generate payload for response generation */ SELECT = '{"model": "phi3:mini", "stream": false, "prompt":"You are acting as a customer advisor responsible for recommending the most suitable products based on customer needs, providing clear and personalized suggestions. Question : ' + @UserInput + '\n\nList of Items : ' + @ModelInput + '"}'; EXECUTE sp_invoke_external_rest_endpoint @url = 'https://localhost/api/generate', @method = 'POST', = , @timeout = 230, = OUTPUT; PRINT JSON_VALUE(@response, '$.result.response') LangChain You can also have a try with LangChain. Same demo with a small difference, there is no vector index created on the vector store table. The table has been modified, but only for demonstration purposes. Reference: SQLServer | 🦜️🔗 LangChain # PREREQ #sudo apt-get update && sudo apt-get install -y unixodbc # sudo apt-get update # sudo apt-get install -y curl gnupg2 # curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - # curl https://packages.microsoft.com/config/debian/11/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list # sudo apt-get update # sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18 # pip3 install langchain langchain-sqlserver langchain-ollama langchain-community import pyodbc from langchain_sqlserver import SQLServer_VectorStore from langchain_ollama import OllamaEmbeddings from langchain_ollama import ChatOllama from langchain.schema import Document from langchain_community.vectorstores.utils import DistanceStrategy #Prompt for testing _USER_INPUT = 'Which product is best suited for shipping small items?' ############### Params ########################################## print("\033[93mSetting up variables...\033[0m") _SQL_DRIVER = "ODBC Driver 18 for SQL Server" _SQL_SERVER = "localhost\\SQL2K25" _SQL_DATABASE = "WideWorldImporters" _SQL_USERNAME = "lc" _SQL_PASSWORD = "lc" _SQL_TRUST_CERT = "yes" _SQL_VECTOR_STORE_TABLE = "StockItem_VectorStore" # Table name for vector storage _MODIFY_TABLE_TO_USE_SQL_VECTOR_INDEX = True #As vector index not considered currently in langchain and structure does not match vector index requirements _CONNECTION_STRING = f"Driver={{{_SQL_DRIVER}}};Server={_SQL_SERVER};Database={_SQL_DATABASE};UID={_SQL_USERNAME};PWD={_SQL_PASSWORD};TrustServerCertificate={_SQL_TRUST_CERT}" _OLLAMA_API_URL = "https://localhost" _OLLAMA_EMBEDDING_MODEL = "nomic-embed-text:latest" _OLLAMA_EMBEDDING_VECTOR_SIZE = 768 _OLLAMA_SLM_MODEL = "phi3:mini" # Model for SLM queries ################################################################### #Define Ollama embeddings embeddings = OllamaEmbeddings( model=_OLLAMA_EMBEDDING_MODEL, base_url=_OLLAMA_API_URL ) conn = pyodbc.connect(_CONNECTION_STRING) cursor = conn.cursor() #Drop embeddings table if it exists print("\033[93mDropping existing vector store table if it exists...\033[0m") cursor.execute(f"DROP TABLE IF EXISTS Warehouse.{_SQL_VECTOR_STORE_TABLE};") print("\033[93mConnecting to SQL Server and fetching data...\033[0m") cursor.execute("SELECT StockItemId, SearchDetails, UnitPrice FROM Warehouse.StockItems;") rows = cursor.fetchall() print(f"\033[93mFound {len(rows)} records to process\033[0m") # Create documents from the fetched data documents = [ Document( page_content=row.SearchDetails, metadata={ "StockItemId": row.StockItemId, "UnitPrice": float(row.UnitPrice) # Convert Decimal to float } ) for row in rows ] conn.commit() #Creating vector store print("\033[93mCreating vector store...\033[0m") vector_store = SQLServer_VectorStore( connection_string=_CONNECTION_STRING, distance_strategy=DistanceStrategy.COSINE, # If not provided, defaults to COSINE embedding_function=embeddings, embedding_length=_OLLAMA_EMBEDDING_VECTOR_SIZE, db_schema = "Warehouse", table_name=_SQL_VECTOR_STORE_TABLE ) print("\033[93mAdding to vector store...\033[0m") try: vector_store.add_documents(documents) print("\033[93mSuccessfully added to vector store!\033[0m") except Exception as e: print(f"\033[91mError adding documents: {e}\033[0m") #Vector index not yet integrated in SQL Server VectorStore (drop auto-created nonclustered PK and generating int clustered PK if (_MODIFY_TABLE_TO_USE_SQL_VECTOR_INDEX): print("\033[93mModifying structure to create vector index...\033[0m") cursor.execute("DECLARE @AutoCreatedPK sysname, @SQL nvarchar(max);" f"SELECT @AutoCreatedPK = name FROM sys.key_constraints WHERE type = 'PK' AND parent_object_id = object_id('Warehouse.{_SQL_VECTOR_STORE_TABLE}');" f"SELECT @SQL = 'ALTER TABLE Warehouse.{_SQL_VECTOR_STORE_TABLE} DROP CONSTRAINT ' + @AutoCreatedPK + ';'" "EXEC sp_executesql @SQL;" f"ALTER TABLE Warehouse.{_SQL_VECTOR_STORE_TABLE} ADD Alt_Id int identity(1,1);" f"ALTER TABLE Warehouse.{_SQL_VECTOR_STORE_TABLE} ADD CONSTRAINT PK_{_SQL_VECTOR_STORE_TABLE} PRIMARY KEY (Alt_Id);") conn.commit() print("\033[93mCreating vector index...\033[0m") cursor.execute(f"CREATE VECTOR INDEX IV_{_SQL_VECTOR_STORE_TABLE} ON [Warehouse].[{_SQL_VECTOR_STORE_TABLE}] (embeddings) WITH (METRIC = 'cosine', TYPE = 'DiskANN');") conn.commit() #Generate prompt then answer print(f"\033[92mUser Input: {_USER_INPUT}\033[0m") context = [ { "Item": doc.page_content, "UnitPrice": doc.metadata.get("UnitPrice", None) } for doc in vector_store.similarity_search(_USER_INPUT, k=3) ] llm = ChatOllama(model=_OLLAMA_SLM_MODEL,base_url=_OLLAMA_API_URL) prompt = ( f"You are acting as a customer advisor responsible for recommending the most suitable products based on customer needs, providing clear and personalized suggestions" f"Context: {context}\n\nQuestion: {_USER_INPUT}\n\n") response = llm.invoke(prompt) print(f"\033[36m{response.content}\033[0m") Note : If using devcontainer with VSCode add "runArgs": [ "--network=host" ] to devcontainer.json to allow connections to “localhost”. Import and install the previously created certificat docker cp C:\Docker\Ollama\localhost.crt <devcontainer name>:/usr/local/share/ca-certificates/localhost.crt docker exec <devcontainer name> "update-ca-certificates" Disclaimer The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.Create PowerApps Application with On-premises SQL Server
PowerApps is a one of the powerful low code applications from Microsoft, which helps to develop millions of applications within a minute. Only platform which allows technical and non-technical to implement application with their requirements. Power platforms can be developed to receive data from different data sources like Excel, SQL server, share point resources and data from different cloud storages like Google, OneDrive, etc. Considering OnPrem data sources we do traditional coding to connect the application with the data source. But here we have a possibility to develop a PowerApps and receive data from On Prem SQL server. In this article I will show you how to connect OnPrem SQL to PowerApps. Pre-Requites 1) OnPrem SQL Server 2) PowerApps Subscription 3) On-Premises Data Gateway 4) Internet Connection On-Premises Data gateway for Power Apps On-Premises gateway is a bridge which provides most secure communication in between local sources to cloud applications. To know and learn more about On-Premises data gate way: https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/gateway-reference SQL Server on Windows 10 Machine I have SQL database installed on my Windows 10 machine, which is a local machine with the active internet connection. If you are new to SQL server installation and configure, I have shared some other posts which will show you how to install and configure SQL server 2022 in your test environment. Download SQL Server 2022: https://info.microsoft.com/ww-landing-sql-server-2022.html?culture=en-us&country=us To Confirm your Installed Version: SELECT @@VERSION I have created a table named Demo Data and inserted some sample data Let’s configure the On-Premises Data Gateway To Download the On-Premises Data Gateway for PowerApps please download the Gateway from below link — https://powerapps.microsoft.com/en-us/downloads/. Once it’s downloaded install it to the local machine, it will take some time to install. When installation succeed it will prompt a dialog box to enter the Microsoft account details. Use the same account which you used to create PowerApps. In the next step create a Gateway with the valid security and recovery key. Here I named my gateway as DEMOPOWERAPPS Create Configure button to apply the configurations and close the dialog box, now it’s done. Now it’s time to build the app in PowerApps Go to PowerApps portal to make the app — make.powerapps.com I am going to create the PowerApps with SQL data source Click the SQL and add the New Connection and select the connection source as SQL database (SQL Server). In the authentication type select SQL Server Authentication from the drop-down list. In the source selection pane select Connect using on-premises data gateway as data source for SQL server SQL Server Name — Windows 10 Local PC Name SQL Database Name- POWERAPPSDEMO Username- demouser Password-[Your SQL User Password Here] In the bottom you need to select the installed gateway, here it will show the gateways you installed with the Microsoft account which you used to create the PowerApps. Here my installed DEMOPOWERAPPS gateway is listed and it’s showing, if your gateway is not listed, please refresh the gateway list. Once you select the correct gateway click create to link the data source to PowerApps. It will show the available tables once the gateway connection is succeeded, here my table DemoData is showing. I am going to use the DemoData table to connect with PowerApps. In case in the same database if you want to change or create new table you can either create it from here or choose different one. Click connect to link the On Prem database with PowerApps, It’s done and app is automatically created now. Without doing any additional changes in the app I will see the preview of the app to check to see whether it’s communicating correctly with the SQL database available at my laptop. The data which I entered manually using SQL commands are appearing here, it means it’s perfectly connected with the on prem SQL database. By adding an edit form in the PowerApps you can be able to insert the data from PowerApps to On Prem SQL Server. Just try this by yourself and let me know if you have any issues in connect On Prem SQL server to Power apps. Happy Coding!!
How Can a Company Receive Support from Microsoft for SQL Server Enterprise with Software Assurance?
Hello, I’m currently managing SQL Server under the following licensing agreement: SQL Server Enterprise Core Single Language License & Software Assurance Open Value | 2 Licenses | No Level | 1 Year | Acquired Year 1 | AP I’ve been informed that Software Assurance (SA) no longer includes technical support for SQL Server. Could you please confirm if this is correct? If our organization needs technical support from Microsoft for SQL Server, I would like to clarify the following: Is it mandatory to have a Unified Support contract or to purchase incidents via the Microsoft Services Hub in order to receive support? Regarding Services Hub, I’ve heard that support incidents must be purchased using a personal Microsoft account (MSA). If this is true, can this method be used to receive support for corporate environments? Thank you in advance.The Microsoft.Build.Sql project SDK is now generally available
Your database should be part of a wholistic development process, where iterative development tools are coupled with automation for validation and deployment. As previously announced, the Microsoft.Build.Sql project SDK provides a cross-platform framework for your database-as-code such that the database obejcts are ready to be checked into source control and deployed via pipelines like any other modern application component. Today Microsoft.Build.Sql enters general availability as another step in the evolution of SQL database development. Standardized SQL database as code SQL projects are a .NET-based project type for SQL objects, compiling a folder of SQL scripts into a database artifact (.dacpac) for manual or continuous deployments. As a developer working with SQL projects, you’re creating the T-SQL scripts that define the objects in the database. While the development framework around SQL projects presents a clear build and deploy process for development, there’s no wrong way to incorporate SQL projects into your development cycle. The SQL objects in the project can be manually written or generated via automation, including through the graphical schema compare interfaces or the SqlPackage extract command. Whether you’re developing with SQL Server, Azure SQL, or SQL in Fabric, database development standardizes on a shared project format and the ecosystem of tooling around SQL projects. The same SQL projects tools, like the SqlPackage CLI, can be used to either deploy objects to a database or update those object scripts from a database. Free development tools for SQL projects, like the SQL database projects extension for VS Code and SQL Server Data Tools in Visual Studio, bring the whole development team together. The database model validation of a SQL project build provides early verification of the SQL syntax used in the project, before code is checked in or deployed. Code analysis for antipatterns that impact database design and performance can be enabled as part of the project build and extended. This code analysis capability adds in-depth feedback to your team’s continuous integration or pre-commit checks as part of SQL projects. Objects in a SQL project are database objects you can have confidence in before they’re deployed across your environments. Evolving from original SQL projects SQL projects converted to the Microsoft.Build.Sql SDK benefit from support for .NET 8, enabling cross-platform development and automation environments. While the original SQL project file format explicitly lists each SQL file, SDK-style projects are significantly simplified by including any .sql file in the SQL projects folder structure. Database references enable SQL projects to be constructed for applications where a single project isn’t an effective representation, whether the database includes cross-database references or multiple development cycles contribute to the same database. Incorporate additional objects into a SQL project with database references through project reference, .dacpac artifact reference, and new to Microsoft.Build.Sql, package references. Package references for database objects improve the agility and manageability of the release cycle of your database through improved visibility to versioning and simplified management of the referenced artifacts. Converting existing projects The Microsoft.Build.Sql project SDK is a superset of the functionality of the original SQL projects, enabling you to convert your current projects on a timeline that works best for you. The original SQL projects in SQL Server Data Tools (SSDT) continue to be supported through the Visual Studio lifecycle, providing years of support for your existing original projects. Converting an existing SQL project to a Microsoft.Build.Sql project is currently a manual process to add a single line to the project file and remove several groups of lines. The resulting Microsoft.Build.Sql project file is generally easier to understand and iteratively develop, with significantly fewer merge conflicts than the original SQL projects. A command line tool, DacpacVerify, is now available to validate that your project conversion has completed without degrading the output .dacpac file. By creating a .dacpac before and after you upgrade the project file, you can use DacpacVerify to confirm the database model, database options, pre/post-deployment scripts, and SQLCMD variables match. The road ahead With SQL Server 2025 on the horizon, support for the SQL Server 2025 target platform will be introduced in a future Microsoft.Build.Sql release along with additional improvements to the SDK references. Many Microsoft.Build.Sql releases will coincide with releases to the DacFx .NET library and the SqlPackage CLI with preview releases ahead of general availability releases several times a year. Feature requests and bug reports for the DacFx ecosystem, including Microsoft.Build.Sql, is managed through the GitHub repository. With the v1 GA of Microsoft.Build.Sql, we’re also looking ahead to continued iteration in the development tooling. In Visual Studio, the preview of SDK-style SSDT continues with new features introduced in each Visual Studio release. Plans for Visual Studio include project upgrade assistance in addition to the overall replacement of the existing SQL Server Data Tools. In the SQL projects extension for VS Code, we’re both ensuring SQL projects capabilities from Azure Data Studio are introduced as well as increasing the robustness of the VS Code project build experience. The Microsoft.Build.Sql project SDK empowers database development to integrate with the development cycle, whether you're focused on reporting, web development, AI, or anything else. Use Microsoft.Build.Sql projects to branch, build, commit, and ship your database – get started today from an existing database or with a new project. Get to know SQL projects from the documentation and DevOps samples.
REPL55012 error
hello. i tryin make db replication(publisher - mssqlserver2019standard, subscriber - mssqlserver 2019 express). at step when "magic should make things" i got error: Get help: https://help/MSSQL_REPL55012 it so strange" in sql forums MS forbidden post exact error message. any directions where to dig? thanks. ps. i tryin reinnstall latest ole db drivers but no luck.Using Entra ID Authentication with Arc-Enabled SQL Server in a .NET Windows Forms Application
Introduction: This guide demonstrates how to securely connect a .NET Framework Windows Forms application to an Arc-enabled SQL Server 2022 instance using Entra ID (Azure AD) authentication. It covers user authentication, token management, and secure connection practices, with code samples and screenshots. In many modern applications, it is common practice to use an application web service to mediate access to SQL Server. This approach can offer several advantages, such as improved security, scalability, and centralized management of database connections. However, there are scenarios where directly connecting to SQL Server is more appropriate. This guide focuses on such scenarios, providing a solution for applications that need direct access to SQL Server. This model is particularly useful for applications like SQL Server Management Studio (SSMS), which require direct database connections to perform their functions. By using Entra ID authentication, we can ensure that these direct connections are secure and that user credentials are managed efficiently. By following the steps outlined in this guide, developers can ensure secure and efficient connections between their .NET Windows Forms applications and Arc-enabled SQL Server instances using Entra ID authentication. This approach not only enhances security but also simplifies the management of user credentials and access tokens, providing a robust solution for modern application development. SAMPLE CODE: GitHub Repository Prerequisites Arc-enabled SQL Server 2022/2025 configured for Entra ID authentication Entra ID (Azure AD) tenant and app registration .NET Framework 4.6.2 Windows Forms application (Not required .NET version, only what the solution is based on) Microsoft.Identity.Client, Microsoft.Data.SqlClient NuGet packages Application Overview User authenticates with Entra ID Token is acquired and used to connect to SQL Server Option to persist token cache or keep it in memory Data is retrieved and displayed in a DataGridView Similar setup to use SSMS with Entra ID in articles below. Windows Form Sample Check User Button shows the current user The Connect to Entra ID at Login button will verify if you are logged in and try to connect to SQL Server. If the user is not logged in, an Entra ID authentication window will be displayed or ask you to log in. Once logged in it shows a Connection successful message box stating the connection to the database was completed. The Load Data button queries the Adventure Works database Person table and loads the names into the datagridview. The Cache Token to Disk checkbox option either caches to memory when unchecked and would require reauthentication after the application closes, or the option to cache to disk the token to be read on future application usage. If the file is cached to disk, the location of the cached file is (C:\Users\[useraccount]\AppData\Local). This sample does not encrypt the file which is something that would be recommended for production use. This code uses MSAL (Microsoft Authentication Library) to authenticate users in a .NET application using their Microsoft Entra ID (Azure AD) credentials. It configures the app with its client ID, tenant ID, redirect URI, and logging settings to enable secure token-based authentication. //Application registration ClientID, and TenantID are required for MSAL authentication private static IPublicClientApplication app = PublicClientApplicationBuilder.Create("YourApplicationClientID") .WithAuthority(AzureCloudInstance.AzurePublic, "YourTenantID") .WithRedirectUri("http://localhost") .WithLogging((level, message, containsPii) => Debug.WriteLine($"MSAL: {message}"), LogLevel.Verbose, true, true) .Build(); This method handles user login by either enabling persistent token caching or setting up temporary in-memory caching, depending on the input. It then attempts to silently acquire an access token for Azure SQL Database using cached credentials, falling back to interactive login if no account is found. private async Task<AuthenticationResult> LoginAsync(bool persistCache) { if (persistCache) TokenCacheHelper.EnablePersistence(app.UserTokenCache); else { app.UserTokenCache.SetBeforeAccess(args => { }); app.UserTokenCache.SetAfterAccess(args => { }); } string[] scopes = new[] { "https://database.windows.net//.default" }; var accounts = await app.GetAccountsAsync(); if (accounts == null || !accounts.Any()) return await app.AcquireTokenInteractive(scopes).ExecuteAsync(); var account = accounts.FirstOrDefault(); return await app.AcquireTokenSilent(scopes, account).ExecuteAsync(); } Connecting to SQL Server with Access Token This code connects to an Azure SQL Database using a connection string and an access token obtained through MSAL authentication. It securely opens the database connection by assigning the token to the SqlConnection object, enabling authenticated access without storing credentials in the connection string. This sample uses a self-signed certificate, in production always configure SQL Server protocols with a certificate issued by a trusted Certificate Authority (CA). TrustServerCertificate=True bypasses certificate validation and can allow MITM attacks. For production, use a trusted Certificate Authority and change TrustServerCertificate=True to TrustServerCertificate=False. Configure Client Computer and Application for Encryption - SQL Server | Microsoft Learn string connectionString = $"Server={txtSqlServer.Text};Database=AdventureWorks2019;Encrypt=True;TrustServerCertificate=True;"; var result = await LoginAsync(checkBox1.Checked); using (var conn = new SqlConnection(connectionString)) { conn.AccessToken = result.AccessToken; conn.Open(); // ... use connection ... } Fetching Data into DataGridView This method authenticates the user and connects to an Azure SQL Database using an access token, and runs a SQL query to retrieve the top 1,000 names from the Person table. It loads the results into a DataTable, which can then be used for display or further processing in the application. private async Task<DataTable> FetchDataAsync() { var dataTable = new DataTable(); var result = await LoginAsync(checkBox1.Checked); using (var conn = new SqlConnection(connectionString)) { conn.AccessToken = result.AccessToken; await conn.OpenAsync(); using (var cmd = new SqlCommand("SELECT TOP (1000) [FirstName], [MiddleName], [LastName] FROM [AdventureWorks2019].[Person].[Person]", conn)) using (var reader = await cmd.ExecuteReaderAsync()) { dataTable.Load(reader); } } return dataTable; } Configure Azure Arc SQL Server to use Entra ID authentication Using SQL Server 2022 follow the instructions here to setup the key vault and certificate when configuring. This article can also be used to configure SSMS to use Entra ID authentication. Detailed steps located here: Set up Microsoft Entra authentication for SQL Server - SQL Server | Microsoft Learn Using SQL Server 2025 the setup is much easier as you do not need to configure a Key Vault, or certificates as it is relying on using the managed identity for the authentication. Entra ID App Registration Steps Register a new app in Azure AD Add a redirect URI (http://localhost) Add API permissions for https://database.windows.net/.default On the Entra ID app registration, click on API Permissions. Add the API’s for Microsoft Graph: User.Read.All Application.Read.All Group.Read.All Add a permission for Azure SQL Database. If Azure SQL database is not shown in the list ensure that the Resource Provider is registered for Microsoft.Sql. Choose Delegated permissions and select user_impersonation, Click Add permission for the Azure SQL Database. NOTE: Once the permissions are added ensure that you grant admin consent on the items. Security Considerations Never store client secrets in client apps Use in-memory token cache for higher security, or encrypted disk cache for convenience Use user tokens for auditing and least privilege References Microsoft Docs: Azure AD Authentication for SQL Server MSAL.NET Documentation Arc-enabled SQL Server Documentation Conclusion: By following the steps outlined in this guide, developers can ensure secure and efficient connections between their .NET Windows Forms applications and Arc-enabled SQL Server instances using Entra ID authentication. This approach not only enhances security but also simplifies the management of user credentials and access tokens, providing a robust solution for modern application development. *** Disclaimer *** The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.Data Migration - Collation mismatch error during an offline migration
This post explores a collation mismatch error during offline migration from SQL Server Express to Azure SQL. It identifies the root cause—AUTO_CLOSE being enabled—and offers a simple fix to ensure smooth migration using Azure DMS.
