Recent Discussions
SQL Server FCI CSV storage flips multiple times into Online (No Access) state and eventually fails
Dear Team, I'm encountering an issue with our SQL Server multi‑instance failover cluster after applying the OS security patches and restarting the second node. Once the second node comes back online, the Cluster Shared Volume (CSV) briefly flips multiple times into Online (No Access) state and eventually fails.(we can make it online manually, but again flips and failed after sometime). To make the SQL cluster available, we either need to shut down the VM or revert the patch. Before the patching, all cluster roles and SQL instances were moved off the node, and the cluster appeared healthy. The issue only occurs after the reboot of the second node. (first node patched and restarted and everything working fine) OS : Windows Server 2025 Standard Patch tried: KB5075899 (February,2026 ) KB5078740 (March,2026) KB5082063 (April 2026 ) KB5087539 (May 2026) Could you please advise if there are any specific checks or steps we should follow during OS patching to prevent CSV access loss? Is it an issue with the patch or something else? Any insights or recommended actions would be really helpful to perform the security OS patch in the server Thanks you!
Best Practices for Connecting Internal SQL Server Financial Systems to Online Payment Platforms
I currently have an internal enterprise system used for purchasing, payments, and finance operations. The system runs on an on-premises database using Microsoft SQL Server and stores all financial transactions and internal workflow data. We now have a new requirement to enable online payment services for customers. These services will be exposed externally (likely in the cloud) and must interact with the same financial system so that transactions are reflected in our internal database. My main concerns are related to architecture, security, and data synchronization. Key points about the current setup: The core system and database are hosted internally (on-premises). The database contains sensitive finance and payment data. Internal processes depend on the current database structure and workflows. The new requirements: Develop an online payment service accessible over the internet. Ensure transactions from the online service update the internal system. Maintain data integrity and security. Avoid performance issues for the internal system. I’m evaluating a few possible approaches but I’m unsure which is best practice: Allow the cloud payment service to connect directly to the internal SQL Server database through secure networking. Maintain a replicated or read/write copy of the database in the cloud. Use SQL Server replication (transactional or snapshot) between on-prem and cloud. Introduce an API or middleware layer that handles all transactions and updates the internal database. Maintain separate databases and synchronize transactions asynchronously. My main questions: Is it recommended to expose the internal SQL Server database directly to cloud services? Should I use replication, a secondary database, or a service/API layer? What architecture pattern is commonly used for integrating on-prem financial systems with online payment platforms? How can we ensure consistency between internal transactions and online payments? Are there recommended SQL Server features or patterns for this scenario (replication, service broker, CDC, etc.)? Any advice on best practices, architecture patterns, or real-world implementations would be greatly appreciated.
Best architecture to integrate internal SQL Server system with cloud-based online payment services
I currently have an internal enterprise ERP system also need to be integrated with online payments, and finance operations. The system runs on an on-premises database using Microsoft SQL Server 2022 and stores all financial transactions and internal workflow data. We now have a new requirement to enable online payment services for customers. These services will be exposed externally (likely in the cloud) and must interact with the same financial system so that transactions are reflected in our internal database. My main concerns are related to architecture, security, and data synchronization. Key points about the current setup: The core system and database are hosted internally (on-premises). The database contains sensitive finance and payment data. Internal processes depend on the current database structure and workflows. The new requirements: Develop an online payment service accessible over the internet. Ensure transactions from the online service update the internal system. Maintain data integrity and security. Avoid performance issues for the internal system. I’m evaluating a few possible approaches but I’m unsure which is best practice: Allow the cloud payment service to connect directly to the internal SQL Server database through secure networking. Maintain a replicated or read/write copy of the database in the cloud. Use SQL Server replication (transactional or snapshot) between on-prem and cloud. Introduce an API or middleware layer that handles all transactions and updates the internal database. Maintain separate databases and synchronize transactions asynchronously. My main questions: Is it recommended to expose the internal SQL Server database directly to cloud services? Should I use replication, a secondary database, or a service/API layer? What architecture pattern is commonly used for integrating on-prem financial systems with online payment platforms? How can we ensure consistency between internal transactions and online payments? Are there recommended SQL Server features or patterns for this scenario (replication, service broker, CDC, etc.)? Any advice on best practices, architecture patterns, or real-world implementations would be greatly appreciated.
SQL Server issue but don't know what - please help!
Hi, I'm facing a SQL Server focused issue that I don't understand why it's occurring and would like your help to identify and resolve, please. I will provide an in-depth breakdown of the scenario. Two years ago, I created a Azure Data Factory (ADF) Pipeline to take data from Azure Synapse to Azure SQL Server, and two other Pipelines to take data from an On-Premises Sage server to the same Azure SQL Server. These Pipelines were working perfectly up until two days ago (11/03/26) when the Pipelines still always complete successfully but the duration have increased greatly. The below screenshot shows the log of these three Pipeline for the past week. Note how prior to the 12/03/26 the Synapse Pipeline took about 6 minutes to complete and the two Sage Pipelines took around 25 seconds. Also note how on the 12/03/26 the Synapse Pipeline took over 3 hours but the two Sage Pipelines continued with their normal 25 seconds. Notice today (13/03/26) the Synapse Pipeline was still slow but now the two Sage Pipelines are taking over an hour. I'll note here that the Pipelines still complete successfully (so the config must be correct as it has been for two years). Each Pipeline contains a single (for Sage) or several (for Synapse) 'Copy data' objects. The objects have their source configuration to simply extract from the source (so either Synapse or Sage) and then the sink configuration has a 'Pre-Copy script' which simply Truncates the target SQL Server table, before loading to the SQL Server table. The screenshot below is one example - each Pre-Copy script is populated the same but just with the tables being different. When I look at the log for the Pipelines, I see a common theme for each and that is the 'Pre-Copy script' (the Truncate) is consuming 99% of the time. The screenshot below shows this common theme. So at this point, I ask the question why, after two years of all working well and completing very quickly, are the Pipelines taking so long to complete? This also seems to be an intermittent problem as I have performed several manual executions which will take a long while, then revert back to the quick several minutes again (so good), then revert once more to taking a long while again. It is intermittent. See the screenshot below. Notice how the same Pipeline have different durations. It's intermittent. The reason why I think it is a SQL Server focused issue for the following reasons: 1) Synapse - Performing simple commands (Select Top) returns data within seconds. 2) SQL Server - Performing simple commands (Select Top) usually takes seconds but during the past two days is often taking half hour. 3) SQL Server - When amending a SQL View it would take a split second to complete. During the past two days, it goes over ten minutes and still doesn't complete (I was amending the View for testing purposes). 4) SQL Server - When looking at the Views node under a database, during the past two days, it intermittently doesn't show the View. Sometimes, it will work if I log out and then back into SQL Server. 5) Power BI - Refreshing a Power BI report, whether from the Power BI Service or Desktop, call SQL Server Views. These SQL Server Views read from several SQL Server tables. These report refreshes are failing due to an IDbCommand interface error. These Power BI report data refreshes simply read from a SQL Server table via a SQL View. They don't reference or consider at all the any ADF Pipelines. The Pipelines execute between 2am and 3am each morning. The Power BI report refresh their data around 12pm to 5pm. As SQL Server seems to be problematic at the point of the 'Pre-Copy script', which is one of the end to end process, and then problematic at the very other end where Power BI reports consumes SQL Views - leads me to believe the issue is with SQL Server. I am a report developer and not an ADF expert. I've built the ADF process on the side. I haven't changed any development/configuration/etc... between this all working and failing. Our I.T. department have advised they have made no changes to contribute to this issue. Please can someone advise on what's happening here and why this issue has arose when for two years all was fine? Thanks.
Polybase - Enforce TCP/IP Protocol
Hello, We know Polybase service in SQL server by default makes use of shared memory protocol to connect SQL server (local). I would like to know if there is any way we can change or force the connections to make use of TCP / IP protocol. Any help would be appreciated.
Service Broker on Ubuntu 20.04 (Docker) cryptography error
Hi, Has anyone successfully created a multi‑instance / multi‑host SQL Server Service Broker implementation on Linux, specifically on distributions using OpenSSL 3? I have a fully working Service Broker environment on SQL Server 2017, running in Docker on Ubuntu 18.04 (OpenSSL 1.1.1). My environment has 6 containers, arranged to simulate two datacenters: DC1: Primary AG replica + synchronous replica + async replica DC2: Same setup 7 databases in the Availability Group Service Broker dialogs between databases on the same SQL instance work perfectly. This confirms: Database‑level dialog security works Certificate‑based authentication between database principals is correct DMKs are auto-opened and protected by SMK All dialogs use BEGIN DIALOG with certificates valid and present Cross-instance messages work correctly. I have: Broker endpoints using certificate‑based endpoint authentication Proper certificate exchange (private key on owning instance, public key on the peer) Routes defined correctly Remote Service Bindings used to force dialog security Certificates created dynamically with correct validity date ranges DMKs created, opened, and re-encrypted with SMK at runtime Scripts that fully automate certificate creation, export, import, user creation, RSB creation, etc. Everything here works fine as long as I stay on Ubuntu 18.04 (OpenSSL 1.1.1). The problem: If I switch only the base Linux image—to Ubuntu 20.04, Ubuntu 22.04, or RHEL 8—and change nothing else, then all cross‑instance Service Broker dialogs fail with: An exception occurred while enqueueing a message in the target queue. Error: 9641, State: 122. A cryptographic operation failed. This error indicates a serious problem with SQL Server. This happens on SQL Server 2019 and 2022, both CU up-to-date, both using OpenSSL 3. Key points: Intra‑instance dialog security still works Endpoint transport security still works (i.e., endpoints authenticate and connect) The failure occurs only when creating session keys for cross‑instance dialog security The failure happens even when using: encryption = off fresh DMKs fresh certificates certificates stored in master or user DB key length 2048 or 3072 different validity periods So far I have: Confirmed DMKs exist on both instances Confirmed DMKs are encrypted by SMK Confirmed DMKs auto-open (is_master_key_encrypted_by_server = 1) Manually opened DMKs before creating certificates Recreated SMK/DMK on clean containers Everything behaves as expected. Repeatedly rebuilt all of the following: Endpoint authentication certificates Dialog security certificates Remote Service Bindings Database principals Routes Service Broker services, queues, and message types I’ve verified: All certificate subjects match All public keys export/import correctly Private keys exist where they should exist Thumbprints match across both sides No old certificates remain in master or user DBs Modified the OpenSSL behaviour to legacy (1.1.1) behaviour, e.g. [openssl_init] providers = provider_sect ssl_conf = ssl_sect alg_section = evp_properties [evp_properties] rh-allow-sha1-signatures = yes fips_mode = no [provider_sect] default = default_sect legacy = legacy_sect [default_sect] activate = 1 [legacy_sect] activate = 1 [ssl_sect] system_default = system_default_sect [all_policy] rsa_pkcs1_padding_check = 0 [system_default_sect] # Lowest policy to allow legacy algorithms (including SHA-1, 1024-bit RSA) CipherString = DEFAULT:@SECLEVEL=0 To rule out network/transport issues, I validated: Endpoints authenticate each other Connections appear in sys.dm_broker_connections STATE = OPEN Transport security appears to be working on OpenSSL 3. I confirmed: Messages flow when RSB is removed and encryption=off is used Messages fail only when RSB is enabled, which requires certificate‑based dialog security The target instance claims “private key missing” even though it is present and readable Has anyone successfully created a multi‑instance / multi‑host SQL Server Service Broker implementation on Linux, specifically on distributions using OpenSSL 3? If so, any guidance on how to resolve the issues i am facing? Many thanks, AndrewCompat level 90: XML string-to-datetime UDF
Hello, I’m testing a behavior described in SQL Server documentation for **database compatibility level 90**. The docs state that a user-defined function that converts an XML constant string value to a SQL Server date/time type is marked as **deterministic**. On **SQL Server 2005**, I’m seeing the opposite: the function is marked as **non-deterministic** (`IsDeterministic = 0`). I’m trying to understand whether I’m missing a requirement/constraint or whether this is a doc mismatch / version-specific behavior. ### Environment - Product: **Microsoft SQL Server 2005** - Database compatibility level: **90** --- ## ✅ Repro script ```sql IF OBJECT_ID('dbo.fn_ParamXmlToDatetime', 'FN') IS NOT NULL DROP FUNCTION dbo.fn_ParamXmlToDatetime; GO CREATE FUNCTION dbo.fn_ParamXmlToDatetime (@xml XML) RETURNS DATETIME WITH SCHEMABINDING AS BEGIN DECLARE @y DATETIME; -- Convert an XML value to DATETIME SET @y = CONVERT(DATETIME, @xml.value('(/r)[1]', 'datetime')); RETURN @y; END GO SELECT OBJECTPROPERTY(OBJECT_ID('dbo.fn_ParamXmlToDatetime'), 'IsDeterministic') AS IsDeterministic, OBJECTPROPERTY(OBJECT_ID('dbo.fn_ParamXmlToDatetime'), 'IsPrecise') AS IsPrecise; GO ``` ### Actual result `IsDeterministic = 0` (non-deterministic) ### Expected result (based on docs) `IsDeterministic = 1` (deterministic) for this pattern under compat level 90. --- ## Questions 1. Are there additional conditions required for SQL Server to mark this UDF as deterministic (for example, specific XQuery usage, avoiding `CONVERT`, using `CAST`, using `datetime2` doesn’t exist in 2005, etc.)? 2. Does the determinism rule apply only when converting from an **XML literal constant** inside the function, rather than an XML parameter value? 3. Is this behavior different for **typed XML** (XML schema collections) vs **untyped XML**? 4. Is this a known difference/bug in SQL Server 2005 where the UDF is functionally deterministic but still reported as non-deterministic by `OBJECTPROPERTY`? Thank you for any clarification. ---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.SQL Server 2017 – CLR was loaded in an unsupported manner (All SSIS jobs failed)
Hi, We are facing a critical issue in our SQL Server 2017 instance. When trying to use a built-in CLR function or running SSIS-related jobs, we are getting the below error: The Common Language Runtime (CLR) was loaded in an unsupported manner. This can occur if an extended stored procedure or OLE Automation object running in SQL Server calls into managed code before the CLR integration runtime host loads the CLR. You need to restart SQL Server to use CLR integration features. Steps tried so far: Restarted SQL Server service Restarted the entire Windows Server Verified .NET Framework version (4.7.03062 installed) Confirmed CLR integration is enabled (sp_configure 'clr enabled', 1) All SSIS jobs are failing due to this issue. Any suggestions, please?
Problem with differential backups, after a problem with a full backup
I have a database in Microsoft SQL server 2008 R2, in which I have configured the maintenance plan for backups (a full back on Tuesdays, Thursdays and Saturdays at 00, three transactional logs at 4, 5 and 6 am, and then at 7 am a differential, and so on, finishing with a differential backup at 11 PM). At the beginning of July, there was a problem with a full backup that was not done, because of lack of space (this problem was July 5th), then the differentials began to increase in size, up to 10 GB, when the full was done, the differentials decreased in size to 1 GB (before this, the differentials had a maximum size of 800 MB), the problem is that every day it increases in size, until today, when each differential weighs 6 GB, the full backup's size is about 96 GB because the database is too old. and I've seen some strange behavior, when the plan gets to the differentials, at 7:46 (for example), the file is finished creating, and it weighs 500mb, but then, 1 minute later, it's like the differential is overwritten, and there it increases in size up to 3GB. What could be causing the error? Maybe it's due to something with the TRUNCATE of the full backup? How can I solve this? I've already tried doing a new manual full backup (on a day that it's not done, at 00 am), and at the moment that it finished being done, a new manual differential, but that didn't solve it. The queries that I run: FULL BACKUP: BACKUP DATABASE [xxx] TO DISK = N'\xxx\SQLServerDatabases\Backups\full_reset.bak' WITH INIT, NAME = N'Full_Reset', SKIP, STATS = 10; DIFFERENTIAL BACKUP: BACKUP DATABASE [xxx] TO DISK = N'\xxx\SQLServerDatabases\Backups\diff_reset.dif' WITH DIFFERENTIAL, INIT, NAME = N'Diff_Reset', SKIP, STATS = 10;execution SP via linked server from SQL Job
Hello colleagues, we have next environment: 1. SQL Server standard edition 2022 16.0.4185.3. 2. SQL Server Job service running under domain managed service account. Category type is Data collector. 3. Job owned by [sa] 4. Job execute stored procedure using next command exec RemoteServer.[DB].[dbo].Load_RTL 0 5. Linked server RemoteServer is MS OLEDB Provider and configured to use (Be made using this security context). Login is a SQL Login with provided password. When job is started, it trying to execute remote SP using SQL Server Agent account, but not linked account which I indicated above in line №5. Why and how to change this so remote SP executed using linked server account? Thank you.link server error after switching from SQL authentication to Active directory service account
Hi All, I am trying to switch from using a SQL account to use active directory service acccount selecting impersonate option on a link server. When i try do this i get below errror. any ideas how to fix? ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ TITLE: Microsoft SQL Server Management Studio ------------------------------ The linked server has been updated but failed a connection test. Do you want to edit the linked server properties? ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Access to the remote server is denied because no login-mapping exists. (Microsoft SQL Server, Error: 7416) For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-7416-database-engine-error ------------------------------ BUTTONS: &Yes &No ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CSV file size from "Save Results As..." versus exported via SSIS Package / Export Wizard
When exporting query results from the grid using "Save Results As...", I'm consistently creating CSV files that are half the size of CSV files created by exporting via the Import/Export Wizard (and the resulting SSIS Package). Exact same query used for both, same results, same number of records etc. I believe this has to do with the file encoding used by each export process. I know this is not a precise science, but based on how Notepad interprets the resulting files, the encoding of the "Save Results As..." files is "UTF-8 with BOM", while the SSIS Package generates a "UTF-16 LE" file. I've tried a variety of approaches to try to get the Import / Export Wizard to mimic the "Save Results As" encoding without success. Changing the Code Page field on the "Choose a Destination" screen to "65001 (UTF-8)" results in the dreaded "DT_NTEXT not supported with ANSI files" error upon export due to nvarchar(max) data types in the source table. That can be resolved by checking the Unicode box on the "Choose a Destination" page, but checking Unicode also disables the Code Page dropdown. Even if you select "65001 (UTF-8)" in Code Page and then check Unicode, it still produces a UTF-16 encoded file. Two questions at this point: 1) How does the "Save Results As..." function in the query grid avoid the DT_NTEXT error and produce a UTF-8 encoded file? 2) If I tried to edit the package in SSIS Designer, would I have more control over the encoding and be able to mimic the "Save Results As..." file sizes? This would take a bit of effort (installing Visual Studio data tools, learning how to use it, etc) so if that's a dead end I'd rather not pursue it.SQL connector for Azure Key Vault on Linux
Hi everybody, after having successfully configured EKM (to access Azure Key Vault) and encrypted a database with a asymmetric key on a SQL running on a windows VM, i am trying to replicate the same configuration in SQL (2022, CU19) running in a linux container. I cannot find the CRYPTOGRAPHIC PROVIDER (from the documentation it should be built-in), nor create it from file (tried in many ways using the windows dll, i get an error 33027 without additional informations). The guide bellow (see last link) suggest to skip step 3 and 4, and proceed with step 5. But the next steps (create credential, create asymmetric key) requires to refer the cryptographic provider. Can someone supply more information for this step? Thks in adv. Stefano Links: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/setup-steps-for-extensible-key-management-using-the-azure-key-vault?view=sql-server-ver16&tabs=portal) https://techcommunity.microsoft.com/blog/sqlserver/enabling-azure-key-vault-for-sql-server-on-linux/4091248Sql Server complexity
For someone who has worked in PostgreSQL env for a very long time, learning the ropes of Sql server looks like some task. I practice with SSMS everyday, creating DB objects - schemas, tables, views, indexes, triggers, sequences, users, roles. And have noticed that Sql server code is more verbose, complex and not as straightforward as PostgreSQL. The use of 'stored procedures' for modifying column/table name and 'severity level' and 'state number' parameters for triggers was really topsy-turvy. I guess, it will take some time before I get used to the 'proprietary' way of doing things.Pet project on SQL Server 2022 platform
Hello, world! I would like to share my pet project on SQL Server 2022 platform. I have created a DWH solution that includes many MS's best practices and interesting features such us: ETL process with data cleansing and MDM that easy expand Documentation CI/CD Functional ETL test Ready analytical templates Time intelligence New & returning customers Cluster customers based on spending volume Product ABC classification Basket analysis Events in progress https://dev.azure.com/zinykov/NorthwindBI Unfortunately in SQL Server 2025 will be no DQS & MDS...
