Forum Widgets
Latest Discussions
Access to SQL Jobs
Hi, I have several jobs in SQL Server Agent. In the department there are 3 people who need administration access to the jobs. What kind of security access must they have? They already have SQL Agent Operator, SQL Agent Reader and SQL Agent User. As far as I know you can only edit jobs where you are the Owner. But, all 3 people must be able to edit all jobs. How to solve that issue?
Synchronize SQL database between two servers
Questions (to make right the synchronize SQL database between two servers) 1. Is required to have SQL Enterprise in both servers, for to be able the synchronization? Or will be enough to have SQL Enterprise in server1. And in server2 with only SQL Express, please? 2. Which requirements are required to fulfil or prepare for to be able synchronize SQL database between two servers, please? 3. Which possibilities (when are more possible solutions) have existed for synchronize SQL database (totally awaiting between four servers), please? 4. Exist a manual for reading an synchronization of SQL server - settings and steps? Thanks in advance.
SQL Server AG Failover - Automatic Failover
Hello, I am looking for a straight and definitive answer that I was hoping someone could answer for me. I want to trust what Copilot says, but I really need to hear it from Microsoft and I can't find any documentation from Microsoft confirming my question. My Environment: 2 replicas in datacenter 1 1 replica in datacenter 2 All three (3) replicas are set to synchronous-commit mode with automatic failover. I tested the failover manually between all three (3) replicas without issue. When I test the automatic failover - I take down both replicas in datacenter 1 at the same time to simulate a datacenter outage. I look at the replica in datacenter 2 and it is just says (Resolving...) next to the replica name. The replica does not come online and the DB is not moved. When I was searching I couldn't find out why. So I turned to Copilot not solve the issue, but to see if it could point me in the right direction. I tell Copilot my setup and what happened. Copilot responded stating that by design from Microsoft you cannot have more than two (2) replicas set to synchronous-commit mode with automatic failover in a SQL Server AG instance. That if more than two (2) are set for automatic failover. The SQL Server AG will use the first two (2) replicas it sees in its metadata and ignore the rest. Copilot went into detail about why this is designed this way, but the amount of information would make this post longer than it already is. If this is true - then when I took down both replicas in datacenter 1, SQL Server AG only saw those two (2) replicas in datacenter 1 as the available replicas to use for an automatic failover and thus why the replica in datacenter 2 did not come online and the DB not being moved So let's do a test. I brought back up the two (2) replicas in datacenter 1. Then I made a change in the AG proprieties. I set the 2nd replica in datacenter 1 to manual. So 1 replica is set to automatic failover and 1 replica is set to manual failover in datacenter 1. The replica in datacenter 2 is set to automatic failover I then take down both replicas in datacenter 1 again to simulate the "outage" and the replica in datacenter 2 comes online and the DB is moved. So is Copilot right? Can there only be two (2) replicas allowed to have/use automatic failover? I cannot find a definitive answer confirming this. Or is my configuration wrong/missing something and if it is, could you please point me in the right direction on how to get this resolved?
Cannot connect Azure OpenAI Embeddings model to SQL Server 2025
On SQL Server 2025, I am trying to vectorize a table. To set up the ability for SQL Server 2025 to communicate with Azure OpenAI embeddings model, I first created a master key for encryption. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Secret'; GO Then I set up a database scoped credential. CREATE DATABASE SCOPED CREDENTIAL [MyAzureOpenAICredential] WITH IDENTITY = 'HTTPEndpointHeaders', SECRET = '{"api-key":"secret"}'; Then I created an external model. CREATE EXTERNAL MODEL AzureOpenAIEmbeddingsModel WITH ( LOCATION = 'https://{secret}-eastus2.cognitiveservices.azure.com/openai/deployments/text-embedding-3-small/embeddings?api-version=2023-05-15', API_FORMAT = 'Azure OpenAI', MODEL_TYPE = EMBEDDINGS, MODEL = 'text-embedding-3-small', CREDENTIAL = [MyAzureOpenAICredential] ); However, when I run this simple script: DECLARE @text NVARCHAR(MAX) = N'SQL Server 2025 enables AI-powered applications'; DECLARE @embedding VECTOR(1536) = AI_GENERATE_EMBEDDINGS(@text USE MODEL AzureOpenAIEmbeddingsModel); I get this error. The database scoped credential 'MyAzureOpenAICredential' cannot be used to invoke an external rest endpoint. I have read through https://learn.microsoft.com/en-us/training/modules/build-ai-solutions-sql-server/4-integrate-ai-models pertaining to this task. As well as SQL Server 2025 docs for creating a model. I have also read SQL Server 2025 docs for creating https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-scoped-credential-transact-sql?view=sql-server-ver17. I have not found any answers.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.SQL Timestamp Incremental Load Issue
An incremental load is implemented based on timestamp, using a view to update a fact table. • The fact table contains multiple KPIs coming from different source tables. • Two main KPI tables are included directly in the view and drive the incremental logic (their timestamps change). • Additional KPIs come from other source tables, which are LEFT JOINed to the view. During an incremental run: • Main source tables are updated → timestamp changes • Other source tables are NOT updated → timestamp unchanged • Because of the LEFT JOIN, missing values from these tables are returned as NULL • These NULLs overwrite existing values in the fact table Example Fact table (before load) id app_number score 1 333 5 Source tables • source1, source2 → timestamps updated (drive incremental load) • source3 → timestamp unchanged Stored procedure logic MERGE fact_table tgt USING ( SELECT app_number, ISNULL(score, 0) AS score FROM vw_main_kpis v LEFT JOIN source3 s3 ON v.app_number = s3.app_number ) src ON tgt.app_number = src.app_number WHEN MATCHED THEN UPDATE SET tgt.score = src.score; Result (incorrect) id app_number score 1 333 0 Existing data is lost.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, Andrew
