I'm currently assessing a setup of Data warehouse implementation in our environment, based on the requirements: 1. A functional/service ID to be setup to access multiple DB servers of other applications in the company for replication purpose. 2. Password of the service ID cannot be changed and won't expiry Based on some researches, I learned that Windows authentications are always recommended as it is more secure (here and here ). Further exploring on windows authentication, I noticed the guides always linked it to Kerberos authentication, which involve domain controller where the an AD account is being used (link ). It make the ID management easier but due to it is a functional ID, I'm not sure if this is the best approach. There is also an article here mentioned to use a local account instead of domain account when possible. Question: 1. Since it is a service ID, and it will connects to many applications, shall I really avoid domain account? My concern is that the password isn't expiring and it has accesses to most of the DB, the risk is higher if this single account is compromised. 2. Does setting up login using local Windows account in respective DB server provide the same advantages of domain account (e.g. use Kerberos protocol)? 3. Is creating multiple local Windows account the recommended approach based on above requirements? Or if there is any resources regarding this can be shared for me to further study on my own. I hope my questions are subjective but not opinion based/open-ended, and I appreciate for any input and sharing.

**TEST 1 - PostgreSQL with Active Directory authentication** 1. I have installed PotgreSQL Server v15.3 on Red Hat 8.8 and integrate PostgreSQL authentication using Active Directory. 2. In pg_hba.conf I have set only one settings (all other are commented out):

all all all gss

3. On separate Red Hat 8.8 machine I have installed PostgreSQL Client v15.3 and I have successfully connected to PostgreSQL server using Kerberos authentication and Active Directory using psql tool as _user01_. 4. On separate Windows 2022 Server I have set static IP and DNS address and added computer to Active Directory domain and I can successfully login to Windows with user _user01_. On this PC I have installed PostgreSQL Client v15.3 and I have tried to connect to PostgreSQL server using psql tool with command: psql -h server.adexample.com -p 5432 -d postgres but error is returned: psql: error: connection to server at "server.adexample.com" (192.168.100.36), port 5432 failed: FATAL: no pg_hba.conf entry for host "192.168.100.41", user "user01", database "postgres", no encryption **This is the PROBLEM I am trying to solve.** 5. On Windows where PostgreSQL client v15.3 is installed I monitored network traffic with Wireshark and I see psql client first sends "SSL/TLS" request and server refuses, then sends "clear text" request and server returns error (from step 4). From network packets it looks like psql client does not even tries to connect to server using Kerberos. **TEST 2 - IBM Db2 with Active Directory authentication** 1. Separate Red Hat 8.8 I have installed IBM Db2 database server v11.5 and configure it to authenticate with Active Directory using Kerberos. 2. On separate Red Hat 8.8 I have installed IBM Db2 database client v11.5 and I can successfully connect to Db2 server using Kerberos authentication and Active Directory. 3. On the same Windows 2022 Server (that I have already installed PostgreSQL client and Kerberos authentication fails) I have installed IBM Db2 database client v11.5 and I can successfully connect to IBM Db2 database server using Kerberos Active Directory with Windows user 'user01'. I conclude Windows Kerberos is correctly working. **Questions:** 1. What should I do on PostgreSQL Client on Windows to successfully connect to PostgreSQL Server on Red Hat using Active Directory? 2. Is there some setting I additionally have to add to pg_hba.conf on PostgreSQL Server site? 3. Does PostgreSQL Client support native Windows Kerberos implementation or do I need to additionally install some software? If yes, which one?

I'm trying to setup Kerberos constrained delegation to solve my **double hop** problem. --- I'm using standard SQL Server configuration with Virtual Account (NT SERVICE\MSSQLSERVER) as a service account for all my instances. When I'm trying to perform double hop query via Linked Server I get this typical error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

The setup looks like this: *User Computer > HOP > SQL Server A > HOP (Linked Server) > SQL Server B* And my query is just basic Select just for testing. Like this:

Select * From [Server B].[DB].[Scheme].[Table]

--- Using Kerberos Configuration Manager for SQL Server I verified SPNs and delegation settings for my servers. SPNs are configured automatically: And delegation is set to None: I also checked what type of authentication does SQL Server uses. To check this I ran this query on Server A (from this article ):

SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ;

And got NTLM as a response. --- According to this results it seems like I'm missing delegation settings. But my question is how should I go about it? I've read countless articles and post and I still can not figure it out. It seems like it should be possible to just use default Virtual Account for this. But every tutorial I've found talks about either Domain Accounts or Managed Service Accounts. In such cases we have this accounts present inside Active Directory, but what about default virtual accounts? This accounts are local - how do I grant delegation permissions to them? Should I grant delegation permission on the machine? But how? I tried doing this. I went over to Active Directory, opened my Server A computer object and did this: So it should allow Server A to delegate to Server B. But it still does not work. And Kerberos Configuration Manager still shows None as delegation type. I'm using Windows Server 2019 and SQL Server 2019. --- **@EDIT** I tried using different setting. Instead of *"Kerberos only"* I selected *"Use any authentication protocol"* and after like 10 minutes **it started working!**. It all makes sense now as SSMS connection still was done with NTLM the "Kerberos only" settings wouldn't work. But the question is why it is still using NTLM? Is it bad and I should try to change it? If so - how?

I'm configuring Kerberos on Oracle I have 2 machines, one is a server with Oracle DB, the other is a client with oracle client(basic+sqlplus), my sqlnet.ora is like this(on server) SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS, KERBEROS5, NONE) SQLNET.FALLBACK_AUTHENTICATION=TRUE SQLNET.KERBEROS5_CONF_MIT = TRUE ADR_BASE = /var/oracle/app SQLNET.KERBEROS5_CC_NAME = /tmp/krb5cc_ SQLNET.KERBEROS5_KEYTAB = /etc/oracle.keytab SQLNET.KERBEROS5_CLOCKSKEW = 1200 SQLNET.KERBEROS5_CONF=/etc/krb5.conf SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=myservice The keytab is ok, the configuration work and oracle user can access it sqlplus /@myserver.domain/myservice SQL*Plus: Release 21.0.0.0.0 - Production on Tue Apr 12 05:41:58 2022 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Last Successful login time: Tue Apr 12 2022 05:26:22 +02:00 Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 SQL> select sys_context('userenv','authentication_method') from dual; SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD') -------------------------------------------------------------------------------- KERBEROS The problem is on client. I have copied the sqlnet.ora(is identical to the server) in the right place I did kinit(I don't have okinit in the client) and... sqlplus /@myserver.domain/myservice SQL*Plus: Release 21.0.0.0.0 - Production on Tue Apr 12 05:43:44 2022 Version 21.5.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: I have configured the user correctly(on the server the kerberos user can enter without problems), what I miss in the client?

I have what I assume to be a pretty standard setup: 1. ServerA running a web application as ServiceAccount1 2. ServerB running SSIS as ServiceAccount2. 3. ServerC hosting a network share that ServiceAccount1 has permissions to read from The web application initiates an SSIS package execution via SQL commands. The package tries to read a file from the network share and fails due to the double-hop problem (login failed on ServerC, "anonymous login". How can I get this to work? The SPNs are already there, and I've tried granting the delegation permission but SSIS refuses to forward on the credentials.

Named instance MS SSAS don't accept connections after NTLM incoming/outgoing traffic deny. There is SPN for this server like: FQDN SPN: Setspn -s MSOLAPSvc.3/AW-SRV01.AdventureWorks.com:AW-FINANCE AdventureWorks\SSAS-Service NetBIOS SPN: Setspn -s MSOLAPSvc.3/AW-SRV01:AW-FINANCE AdventureWorks\SSAS-Service Also default instance on the same server works correct without any problems. SPN was added same way: Setspn -s MSOLAPSvc.3/AW-SRV01.AdventureWorks.com AdventureWorks\SSAS-Service

at our company we run a MS SQL Server that hosts around 70 databases for various software products that are used by our colleagues. The server is version 2016, 8 cores, 127 GB Ram, lots of disk space. Mostly we use trustedConnctions (AD/Kerberos) to the databases. Usage of the databases varies widely. Some are only for logging, others are heavily queried for reading and writing. One of the (third party) software packages, that is used by around 150 users at peak time, connects to 21 databases simultaneously. From time to we see massive problems in the logs of this software, because it takes eight to 12 seconds to establish each database connection. Sometimes there are even timeouts for the connection. In the Database logs we can't find any clue what could cause this delays. The CPU at the server works around 50% most of the time. The disks are not stressed. Total connections to the server fluctuate between 700 and 900. We are not particularly fond in using the sql-server-profiler or interpreting the logs. What could we do to search for the source of the problem? Which indicators should we monitor? Could there be other factors, outside of the sql server, that we should check? I would be grateful for any help! **Update:** the suggestions helped a lot. I read through the links and tried to understand the different wait-types. On script from sqlskills got me this statisic: The biggest chunk is allways the Preemtive_OS_AuthenticationOPS. Internet says: > PREEMPTIVE_OS_AUTHENTICATIONOPS Description This wait accumulates > while SQL Server is waiting for an Active Directory query to complete. That got me thinking... I changed the test environment of the big software suite from trustedconnection (ActiveDirectory) to SQLServer-User authentication. The results are phenomenal: Nearly no wait times for the database connections!! Massive speed increase within the software. Sadly that is not a possible solution for our production environment, but now i will shift my focus to the Kerberos/SQL Server interactions. Any tips on that? **A little update** for users that stumble over this question: The infrastructure colleagues updated a TrendMicro agent on the sql server, that was several versions behind. From that moment on, things ran nearly smoothly. If that was the underlying cause, or also only a symptom of something else... maybe we will never know....

I'm facing a strange situation with Kerberos and Linked Server (on SQL Server 2019). When I set the "trust this user for delegation to specified service only" and add the target SQL server, it works. (meaning all my SPNs are created) The strange thing is that when I check the "trust this user for delegation to any service", it stops working and I start receving error about "anonymous" (typical to double hop Kerberos issue)... I thought that "trust for any" was the "least secure option". Why does it work with the "most secure" option, but don't with the "least secure" one? Shouldn't the target SPN specified in the "specified service only" be part of the "any" Thank you

I'm getting the below error message when attempting to connect to an SQL Server hosted on an Azure VM.

Network error code 0x2746 occurred while establishing a connection; the connection has been closed. This may have been caused by client or server login timeout expiration. Time spent during login: total 12 ms, enqueued 0 ms, network writes 0 ms, network reads 0 ms, establishing SSL 12 ms, network reads during SSL 8 ms, network writes during SSL 0 ms, secure calls during SSL 3 ms, enqueued during SSL 0 ms, negotiating SSPI 0 ms, network reads during SSPI 0 ms, network writes during SSPI 0 ms, secure calls during SSPI 0 ms, enqueued during SSPI 0 ms, validating login 0 ms, including user-defined login processing 0 ms.

I can connect to the SQL Server from SSMS hosted on the same server as the app and I'm not getting an error messages in the SQL Server error log so I'm confident the issue is isolated to the app but am a bit stumped.

I've just built 2 new Win/SQL 2022 Servers. Everything went ok, and I could connect with SSMS. I then changed both SQL engine services to use a domain acct (my standard practice), I can connect locally, but can no longer connect remotely via SSMS with the following error. > The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0) When SQL runs as a local service it can register an SPN. But when it runs as a domain acct it can't. (I've repeated this several times on both servers). I've also tried running the service using a domain admin acct. I've followed the MS instructions for both automatic and manually setting the SPN. And neither has solved the problem. Has anyone else run into this? Any advice or suggestions would be greatly appreciated. I have tried the Kerberos Config Mgr tool and it can't connect either remotely or locally, It returns the error: > 10/01/2023 11:36:10 am Error: Connect to SQLWMI failed \root\Microsoft\SqlServer\ComputerManagement System.Management.ManagementException: Invalid namespace I have also tried running a SQL repair. The SQL log shows the following warnings after startup: > SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required. > The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/server.domain ] for the SQL Server service. Windows return code: 0xffffffff, state: 53. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

**I am attempting to configure Oracle with Kerberos. I have completed all of the necessary configurations and have obtained a ticket. However, when I run SQLPlus /@ORCL, I receive the following error:** C:\Users\Administrator>sqlplus /@ORCL SQL*Plus: Release 21.0.0.0.0 - Production on Wed Feb 21 05:42:04 2024 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied **I am using a Windows machine that acts as both a server and a client. Below are all the details.** [okinit testuser] C:\Users\Administrator>sqlplus /@ORCL SQL*Plus: Release 21.0.0.0.0 - Production on Wed Feb 21 05:42:04 2024 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied okinit C:\Users\Administrator>okinit testuser Kerberos Utilities for 64-bit Windows: Version 21.0.0.0.0 - Production on 21-FEB-2024 05:40:30 Copyright (c) 1996, 2021 Oracle. All rights reserved. Configuration file : C:\Windows\krb5.ini. Password for testuser@EXAMPLE.COM: [oklist] C:\Users\Administrator>oklist Kerberos Utilities for 64-bit Windows: Version 21.0.0.0.0 - Production on 21-FEB-2024 05:40:49 Copyright (c) 1996, 2021 Oracle. All rights reserved. Configuration file : C:\Windows\krb5.ini. Ticket cache: FILE:C:\Users\Administrator\Documents\kerberos\krb5cache Default principal: testuser@EXAMPLE.COM Valid starting Expires Service principal 02/21/24 05:40:33 02/21/24 15:40:33 krbtgt/EXAMPLE.COM@EXAMPLE.COM renew until 02/22/24 05:40:30 [sqlnet.ora file] SQLNET.AUTHENTICATION_SERVICES= (KERBEROS5, KERBEROS5PRE) SQLNET.KERBEROS5_KEYTAB = C:\Users\Administrator\Documents\keytab\keyfile.keytab SQLNET.KERBEROS5_CC_NAME = C:\Users\Administrator\Documents\kerberos\krb5cache SQLNET.KERBEROS5_CONF = C:\Windows\krb5.ini SQLNET.FALLBACK_AUTHENTICATION = TRUE SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = ORCL [krb5.ini] libdefaults] default_realm = EXAMPLE.COM forwardable = true clockskew = 6000 passwd_check_s_address = false [realms] EXAMPLE.COM = { kdc = hostname.EXAMPLE.COM admin_server = hostname.EXAMPLE.COM default_domain = EXAMPLE.COM } [domain_realm] .EXAMPLE.COM = EXAMPLE.COM EXAMPLE.COM = EXAMPLE.COM .example.com= EXAMPLE.COM example.com = EXAMPLE.COM [keytab generation cmd] ktpass /princ ORCL/hostname.example.com@EXAMPLE.COM /mapuser testuser@EXAMPLE.COM /crypto ALL /ptype KRB5_NT_PRINCIPAL /out C:\Users\Administrator\Documents\keytab\keyfile.keytab /pass password1

I have the infrastructure shown in the diagram below. Web Server 1 is outside the Windows Domain. Web Server 2 is inside the Windows Domain. SQL Server 1 and 2 are both inside the domain. There is a linked server connection from SQL Server 1 to SQL Server 2. Both SQL Servers only accept Windows Authentication, not SQL authentication. Both SQL Servers have a Kerberos SPN setup with unconstrained delegation. A domain user on Web Server 2 has a SQL login configured on both SQL Server 1 and SQL Server 2 and can query data on SQL Server 1 and on SQL Server 2 via the linked server. A local user on Web Server 1 has a local login of the same name and password configured on both SQL Server 1 and SQL Server 2 and can query data on SQL Server 1, but not on SQL Server 2 via the linked server. The following error is given in the SQL Server log: *Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided.* The question: can Web Server 1, outside the Windows Domain, be made to successfully query data from SQL Server 2 via the Linked Server connection on SQL Server 1? If so, how?

I am trying to set up a JDBC using DataSource interface, DB2 database and Kerberos for authentication. I am using IBMs documentation which refers in *Kerberos security with no user ID or password* [link here](https://www.ibm.com/docs/en/db2/11.5?topic=sudsdjs-kerberos-security-under-data-server-driver-jdbc-sqlj#imjcc_cjvjcskb__title__3) . **Code block for DataSource**

DB2SimpleDataSource dataSource = new DB2SimpleDataSource();
            dataSource.setDriverType(4);
            dataSource.setServerName("server");
            dataSource.setPortNumber(portnumber);
            dataSource.setDatabaseName("database name");
            dataSource.setSslConnection(true);
            dataSource.setClientProgramName("program name");
            dataSource.setRetrieveMessagesFromServerOnGetMessage(true);
            dataSource.setSecurityMechanism(dataSource.KERBEROS_SECURITY);

I have the following error in my console: **Error message**

com.ibm.db2.jcc.am.SqlException: [jcc][t4][4.19.49] Unexpected Throwable caught: class com.ibm.db2.jcc.am.le (in unnamed module @0x49097b5d) cannot access class sun.security.action.GetPropertyAction (in module java.base) because module java.base does not export sun.security.action to unnamed module @0x49097b5d. ERRORCODE=-4228, SQLSTATE=null
    at com.ibm.db2.jcc.am.kd.a(Unknown Source)
    at com.ibm.db2.jcc.am.kd.a(Unknown Source)
    at com.ibm.db2.jcc.am.kd.a(Unknown Source)
    at com.ibm.db2.jcc.t4.b.b(Unknown Source)
    at com.ibm.db2.jcc.t4.b.a(Unknown Source)
    at com.ibm.db2.jcc.t4.b.a(Unknown Source)
    at com.ibm.db2.jcc.t4.b.a(Unknown Source)
    at com.ibm.db2.jcc.t4.b.(Unknown Source)
    at com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(Unknown Source)
    at com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(Unknown Source)
    at com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(Unknown Source)
    at test.connection.TestDbConnectionDataSource.main(TestDbConnectionDataSource.java:81)
Caused by: java.lang.IllegalAccessError: class com.ibm.db2.jcc.am.le (in unnamed module @0x49097b5d) cannot access class sun.security.action.GetPropertyAction (in module java.base) because module java.base does not export sun.security.action to unnamed module @0x49097b5d
    at com.ibm.db2.jcc.am.le.a(Unknown Source)
    at com.ibm.db2.jcc.am.le.a(Unknown Source)
    at com.ibm.db2.jcc.t4.b.a(Unknown Source)

I am not using the dataSource.setKerberosServerPrincipal("sample/srvlsj.ibm.com@SRVLSJ.SJ.IBM.COM"); which refers in IBM doc. Moreover, I am using many **realm** which I have to replace the @SRVLSJ.SJ.IBM.COM. For this reason I am using the System.setProperty("java.security.krb5.conf", "path\krb5.conf"); The adove error I can resolve this error if I use in VM arguments this: --add-exports java.base/sun.security.action=ALL-UNNAMED. But, I am searching a way to still work without this in VM arguments. Last details. I am using com.ibm.db2.jcc.DB2SimpleDataSource interface. The version of DB2 is 4.19.49, jdk 17.0.6, Kerberos 5.

I have configured linked servers with *"connections will be made using the login's current security context"*, but can only make use of them when logged on to the server, not from another machine... **Works fine:** SSMS --> SQL_SERVER_A --linked server--> SQL_SERVER_B The test connection to the linked server succeeded **Does not work:** SSMS --> ADMIN_PC --> SQL_SERVER_A ---linked server--> SQL_SERVER_B Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456) I've ran the 'Kerberos Configuration Manager for SQL Server' and everything checks OK and user I'm using is the same. Does someone have an idea what could be the problem?

We are trying to enable Kerberos with our SQL Server Reporting Services reports manager website (for ex, instructions in the article "Configure Windows Authentication on the Report Server ") without any success so far, unfortunately. What we've done up to now: - We've used the Kerberos Configuration Manager for SQL Server to ensure things are OK regarding our SQL Server (The SSRS DB is in the default instance, and the reports manager website is also hosted on the same server). This tool shows no errors or problems with delegation - We've created a domain account to run the SSRS service under, and enabled delegation for it in AD - We originally updated the SSRS configuration file's authentication types to look like this: - Though we have also attempted the above config with only the RSWindowsKerberos node under AuthenticationTypes. I agree with J.D in the comments below - this part is confusing, as it appears to say NTLM will be picked if RSWindowsNegotiate is present, yet this link says to include it to enable Kerberos. - And we've created SPNs for our domain account Regarding SPNs, when we've tried to run SSRS under a domain account, we created SPNs using the following setspn commands: setspn -S HTTP/servername.abc.local abc\SsrsSvc setspn -S HTTP/servername abc\SsrsSvc setspn -S HTTP/reports.abc.com abc\SsrsSvc setspn -S HTTP/reports abc\SsrsSvc Some explanation on the above: clearly, the names have been obscured, but if the company domain is "abc", the network admins have set up "abc.local" internally in AD. For the reports manager website, it's publicly accessible via reports.abc.com (ports 443 and 80 - standard http stuff here). Several of the walkthroughs regarding SPNs for SQL and SSRS linked to an article that SqlMag used to host (still viewable here on wayback machine). The issue we're seeing is that only NTLM is being used by any browser connection, and if we force only Kerberos, we see 401s when accessing reports. (Auth headers for NTLM start with "TlR" - and we're clearly seeing that in Fiddler as we examine the requests.) For troubleshooting, we've logged out the User Account Control attribute in the SSRS trace file, and it's 590336 (which, as far as I can tell, is fine). Alternatively, we have also attempted to run SSRS as the Network Service account (which MS docs say won't need SPNs in this case) - but we get the same result. I'm looking for input on what we try next, or on what we've missed. I'd also be curious to know if the SSRS service should be listed in the Kerberos Configuration Manager for SQL Server (in the SPN tab) - as I only see two line items - both for the SQL Server default instance itself (which both show they're OK). This post has a screenshot showing SSRS listed there (under "Examples of the SPN validations" towards the end).

I am attempting to utilize Kerberos delegation in order to allow Active Directory queries using a Linked Server configured for ADSI to be filtered according to the end-user's Active Directory security rights. (The environment I am in has a heavily-restricted AD) I can successfully use the Linked Server if I configure it with connections will **Be made using this security context** and giving it a service user account with permissions to Active Directory. Any queries against the Linked Server return the expected (limited) results from Active Directory that the service user has access to. *Linked server create script:*

lang-sql
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'DOMAIN\SERVICEACCOUNT',@rmtpassword='***************'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible',  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation',  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation',  @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

However, when I change the setting to connections **Be made using the login's current security context** any queries I submit come back with unhelpful messages:

lang-sql
SELECT * FROM OpenQuery (
ADSI,
'SELECT *
FROM ''LDAP://*****.*****.***/DC=*****,DC=*****,DC=***''
WHERE objectClass = ''User''
')

Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT *
FROM 'LDAP://*****.*****.***/DC=*****,DC=*****,DC=***'
WHERE objectClass = 'User'
" against OLE DB provider "ADSDSOObject" for linked server "ADSI".

I have validated that my connections are coming in via Kerberos:

lang-sql
SELECT auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

Which returns: auth_scheme = KERBEROS In this case, the Microsoft SQL 2022 server is configured to run with a group-MSA. As part of troubleshooting this, I configured the group-MSA account with both constrained and unconstrained delegation:

lang-txt
Set-ADAccountControl -Identity gMSA_SQL$ -TrustedForDelegation $true -TrustedToAuthForDelegation $false
Set-ADServiceAccount -Identity gMSA_SQL$ -Clear 'msDS-AllowedToDelegateTo'

The error messages are the same no matter constrained/unconstrained. The gMSA_SQL$ object has the proper SPNs (gMSA_SQL$ has rights to self-configure SPNs and Microsoft SQL properly does so upon start). In order to test the delegation configuration, I configured a SQL Server Linked Server to another SQL Server that was available. The SQL Server Linked Server worked just fine. I'm beginning to think bouncing against ADSI in order to perform the query is somehow messing up Kerberos. I searched far and wide for any examples/discussion about configuring the ADSI provider using constrained delegation and I found nothing applicable.

Apologies for the length of this but I'm app developer, not a SQL DBA nor network Admin, so I'm out of my area and just wanted to be sure I documented everything as well as I could. This is a problem about authenticating from a client app using Kerberos authentication and a Windows Domain Identity, to a SQL 2022 instance on a host that hosts both SQL 2019 and now SQL 2022. I’ve inherited a problem that was abandoned more than a year ago and got it simply because it involves SQL client connections from an application, or that I’m the old man in group which really doesn’t mean I have more experience, at least not like it used to, but they think it does, so I let them believe it. I’ve solved a number problems that were missed, but I’m still short of making it work because I’m having trouble getting the SPN’s and network protocols correct for Kerberos client connections. Each instances of SQL Server has a unique instance name, the 1st has the instance name “Sql2019”, the 2nd instance “Sql2022” and it’s the 2nd instance that needs Kerberos authentication. One of the things missed by the original group were the client libraries used for OLE DB connections; they were still using SQL Native Client. I have since installed the newer MSOLEDBSQL libraries into the client machines and made the code changes to use Ole DB SQL Connections and other client side components for Kerberos client connections, following MS Documentation on this topic. And not knowing if I was going at this the right way, I installed these client libraries on the machine hosting SQL 2019 and SQL 2022. I believe I have all of the correct elements set up on the client side, I’m not sure about the server side, because I can make an OLE DB connection from the client app and access the application databases, but when a connection is made that requires access to the restricted elements of SQL 2022, such as the Master DB or SSIS when installing and configuring DTS Packages, client authentication fails. The error message I’m getting right now is a SQL Connection timeout SQL Error 87. If I understand MS Docs correctly, the reason for this is because the original SPN setup is no longer valid and or, when an OLE DB client connection initially fails Kerberos authentication, it falls back to NTLM. But SQL 2022 can’t authenticate the NTLM connection because something is incomplete or missing with the SPN’s or something is incorrectly configured with regards to the network protocols used, Named Pipes vs TCP/IP. I’m stuck in this latter area and re-reading the MS Docs is not giving me any insights on where to go next. Here is where things stand as of this post: 1. The SQL Server name is “**SqlServer**” 2. The instance names are “**Sql2019**” and “**Sql2022**”. 3. The host is joined to the domain. 4. The TCP/IP ports for both instances use the default port numbers. 5. The Named Pipes protocol use the default SQL Names assigned at installation. 6. The account that both SQL instances runs under was changed before I was given this task, from NT Service to a domain account named (**SysCred**) and it’s used exclusively for services. That account is a member of the domain wide *Administrators Group*, and I have confirmed that on SQL Server, it is also a member of the *local Administrators Group*. I’m not sure if this is relevant or not, but I have left it as it is. 7. The same can be said for the application that they want accessing SQL 2022 with Kerberos authentication, it too runs under a domain account (**CoreSrvcs**), and at present, that domain account is a member of the *local SQL Server’s Administrators Group*, so I have left it as it is. 8. On SQL Server 2022 I found the following: 9. A SQL Login for the domain account (**SysCred**), who is a member of (*sysadmin, serverdmin*) 10. A SQL 2022 Login for the domain account (**CoreSrvcs**), who is a member of (*sysadmin, serverdmin*) 11. This login is mapped to the application databases, the *Master DB*, and to the *SSIS database*. 12. The SQL Host has AD LDS service installed to synch with the domain controller, and that services appears to be working correctly, I see no error messages in the event logs for it. 13. I queried for SPN’s on the SQL Server and found the following: 14. For the domain service account (**SysCred**) that SQL instances run under 15. *MSSQLSvc/SqlServer.domainame:50895* 16. *MSSQLSvc/SqlServer.domainame:SQL2022* 17. *MSSQLSvc/SqlServer.domainame:1433* 18. *MSSQLSvc/SqlServer.domainame:SQL2019* 19. For the domain account (**CoreSrvcs**) that applications run under: 20. *MSSQLSvc/SqlServer.domainame:50895* 21. *MSSQLSvc/SqlServer.domainame:SQL2022* 22. *MSSQLSvc/SqlServer.domainame:1433* 23. *MSSQLSvc/SqlServer.domainame:SQL2019* 24. In the application components that need to query the *Master DB* or the *SSISSDB*, I changed the code from using the elements in the *System.Data.SqlClient* namespace or the *Microsoft.Data.SqlClient* namespace, to use elements from *System.Data.Ole* as per Microsoft Docs, and as long as I only query the application database, everything works. 25. But when the application attempts to access the *Master DB*, or install and configure DTS packages against the *SSISDB* database, the client connection times out and I see this SQL server error 87. 26. NOTE one of the last changes made per MS Docs was to use a SPN in place of the SQL Server Instance name when setting up an Ole DB Connection, on my last few tries I was using one of the SPN’s listed above, but all produced the same error. My last thought before asking for help was to change the protocol order of SQL Server Configuration Manager, from having TCP/IP first to Named Pipes 1st, not sure this would make a difference, and while I wait for a reply I’ll checkout some trouble shooting tips and tools from https://github.com/microsoft/CSS_SQL_Networking_Tools/wiki

I am trying to enable my users to BULK INSERT / OPENROWSET() a CSV file that is stored on our DFS/cifs network shares. I have MS SQL Server 2016 Standard Edition running as a domain account and that account has access to the desired fileshare. So, if I log in to MSSQL using SQL Authentication it is working.

C:\Temp>sqlcmd -N -S %DB_HOSTNAME% -U %DB_USERNAME% -P %DB_PASSWORD%
1> SELECT
2>   CAST(CONNECTIONPROPERTY('auth_scheme') AS nvarchar(10)) AS auth_type,
3>   COUNT(*) AS NumLines
4> FROM OPENROWSET(
5>    BULK '\\example.org\myshare\path\to\mydata.csv'
6>  , FORMATFILE = '\\example.org\myshare\path\to\mydata.fmt'
7> ) AS f;
8> GO
auth_type  NumLines
---------- -----------
SQL                 73

(1 rows affected)

However, when I connect to MSSQL from another machine using Kerberos / Windows Authentication, I receive the following error:

C:\Temp>sqlcmd -N -S %DB_HOSTNAME% -E
1> SELECT
2>   CAST(CONNECTIONPROPERTY('auth_scheme') AS nvarchar(10)) AS auth_type,
3>   COUNT(*) AS NumLines
4> FROM OPENROWSET(
5>    BULK '\\example.org\myshare\path\to\mydata.csv'
6>  , FORMATFILE = '\\example.org\myshare\path\to\mydata.fmt'
7> ) AS f;
8> GO
Msg 4861, Level 16, State 1, Server DBSERVER, Line 1
Cannot bulk load because the file "\\example.org\myshare\path\to\mydata.csv" could not be opened. Operating system error code 1326(The user name or password is incorrect.).
1> SELECT CAST(CONNECTIONPROPERTY('auth_scheme') AS nvarchar(10)) AS auth_type;
2> GO
auth_type
----------
KERBEROS

(1 rows affected)

Normally, if it's a Kerberos Double-Hop issue, I expect the error to be Access is denied. In this case, I've set up the MSSQLSvc/dbserver.example.org and MSSQLSvc/dbserver.example.org:1433 SPNs, verified that I'm connecting using Kerberos, and enabled Kerberos Constrained Delegation for the mssql domain account to the HOST/FILESERVER1 and HOST/FILESERVER2 SPNs. Any ideas what this error, The user name or password is incorrect., could be about or how I can get more info from Windows or SQL Server? ## Update #1 ## I tried to take DFS out of the equation by using a UNC path directly to one of the file servers, but get the same error:

C:\Temp>sqlcmd -N -S %DB_HOSTNAME% -E
1> SELECT
2>   CAST(CONNECTIONPROPERTY('auth_scheme') AS nvarchar(10)) AS auth_type,
3>   COUNT(*) AS NumLines
4> FROM OPENROWSET(
5>    BULK '\\FILESERVER1.example.org\myshare\path\to\mydata.csv'
6>  , FORMATFILE = '\\FILESERVER1.example.org\myshare\path\to\mydata.fmt'
7> ) AS f;
8> GO
Msg 4861, Level 16, State 1, Server DBSERVER, Line 1
Cannot bulk load because the file "\\FILESERVER1.example.org\myshare\path\to\mydata.csv" could not be opened. Operating system error code 1326(The user name or password is incorrect.).

Same result if I just use the netbios name for the file server instead of the FQDN (ie. \\FILESERVER1\myshare\path\to\mydata.csv) ## Update #2 ## I enabled Keberos event logging on the SQL Server, ran the OPENROWSET() query again, and then found the following in the System Event Log:

Log Name:      System
Source:        Microsoft-Windows-Security-Kerberos
Date:          3/9/2023 12:47:56 PM
Event ID:      3
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      DBSERVER.example.org
Description:
A Kerberos error message was received:
 on logon session 
 Client Time: 
 Server Time: 17:47:56.0000 3/9/2023 Z
 Error Code: 0xd KDC_ERR_BADOPTION
 Extended Error: 0xc0000225 KLIN(0)
 Client Realm: 
 Client Name: 
 Server Realm: EXAMPLE.ORG
 Server Name: cifs/DOMAINCONTROLLER01.example.org
 Target Name: cifs/DOMAINCONTROLLER01.example.org@EXAMPLE.ORG
 Error Text: 
 File: onecore\ds\security\protocols\kerberos\client2\kerbtick.cxx
 Line: 1292
 Error Data is in record data.

I'm trying to configure LinkedServer to use Impersonate. All Servers and PC are connected to the same Domain. What I've configured already: SQLServer1 - First/Middle server: - Configured SPNs for account that is running SQL services - Configured delegation for account that is running SQL services - Configured Delegation for server "Trust this computer for delegation to any service (Kerberos only) SQLServer2 - SecondServer - Configured SPNs for account that is running SQL services - Configured delegation for account that is running SQL services (which I think is not needed but did it anyway) - Configured Delegation for server "Trust this computer for delegation to any service (Kerberos only)(which I think is not needed but did it anyway) Created LinkedServer on SQLServer1 to SQLServer2, in Security tab added AD account that is running SQLServices, added my AD Account and checked 'Impersonate' for both. I've used Kerberos Configuration Manager to verify on both servers and it shows that everything is looking good. **When I'm testing connection while I'm logged on SQLServer1 it passes. When I'm testing it from my PC it returns Error: "The test connection to the linked server failed. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)"** Any help would be greatly appreciated.

I'm trying to solve some mystery in two of my environments. Both are SQL 2019 deployments, however one has Kerberos configured (i.e. SPNs are registered for the instance and port), while the other deployment does not. I've been trying to investigate how this happened but not got to the bottom of it yet. Basically, I would like to understand the requirement for future deployments. Am I supposed to manually create (or request to be created) the SPNs or there is some way in which they get automatically created? For example, could they have been created in the course of building the application tier? I'd appreciate if anyone is able to throw some light on this for me please. Of course I have been researching all about Kerberos authentication and SPNs but if there are any specific articles on this specific question, I'd appreciate a link to it please. Thank you