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 have two servers, ServerA and ServerB, both of which are part of the same domain (test). For both servers, the SQL service account and SQL Agent service account are configured to use the domain account test\SQLAdmin. I want to establish a connection from ServerA to ServerB using Windows Authentication and linked server. For this purpose, I am utilizing the domain user account test\sqljobowner. When I created a linked server using ServerB's FQDN on ServerA, with locallogin=test\sqljobowner and enabled the impersonate flag, the connection test was successful. Next, I set up a SQL Server Agent job on ServerA, configured to run daily. This job has test\sqljobowner as its owner and inserts data into a table on ServerB. However, the job fails at the execution step with the following error: > Executed as user test\sqljobowner. Linked servers cannot be used under > impersonation without mapping for the impersonated login. (Error 7437) Update: For the Server I am using SQL service account is test\SQLADMIN. and in the AD server for the test\SQLADMIN user Delegation is enabled added both server as MSSQLsvc type I am using SQL Server 2022 Developer Edition with CU10. linked server connection:

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.

I'm using SQL Server 2016 (SP1) Standard Edition. I have a linked server that uses Windows Authentication and needs to because it connects to a non-SQL Server database. It uses an OLE database provider. I have a third-party application that needs to use this linked server, but its application connects using SQL Server Authentication. Is there a way to allow a SQL Server login to use this linked server?

I have two separate Active Directory domains (FOO and BAA) A dedicated backup server with S.O Windows exists in the FOO domain. A SQL Server instance in the BAA domain needs to store backups on this. The BAA SQL Server accesses the NAS via a dedicated network interface (192.168.137.1). (cross cable) The NAS has a shared folder: '\\192.168.137.1\Backup' From Windows Explorer on the BAA SQL Server, I can create and delete files in this share. However, when I run the following backup script in SQL Server Agent:

DECLARE @DATA CHAR(255)
SELECT @DATA = N'\\192.168.137.1\db_backup_test.bak'
BACKUP DATABASE [test_database] 
TO DISK = @DATA 
WITH NOFORMAT, INIT, 
NAME = N'FULL BACKUP', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

I get an error:

Executed as user: {I tried many users here}. Cannot open backup device '\192.168.137.1\backup\Acesso.bak'. Operating system error 5 (Access is denied.).

I tried to enable access for users on the backup server (both in sharing and security), but since they are not from the same domain, the users do not appear. Since the administrator user of the machine with SQL Server can create and delete files through Windows Explorer, I tried changing the logon of the user in the SQL Agent service to this user. I also tried assigning a drive letter to the path using net use z: \\192.168.137.1\backup (both with and without setting users), but the SQL Agent cannot find the Z: drive. is there a way?

I am trying to connect to my Oracle DB with Windows authentication, and it just doesn't seem to work - Whatever I do I keep getting "ORA-01017: invalid username/password; logon denied". I installed Oracle DB version 21c, and upon that created a new windows user as the Oracle Home User. My global DB name is orcl.ad11.siemens.net, and the PDB is orclpdb. I've created a new listener with the following configuration in **listener.ora**: LISTENER_2 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522)) (ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY)))) SID_LIST_LISTENER_2 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCLPDB.AD11.SIEMENS.NET) (ORACLE_HOME = C:\Oracle\OraDB21c\WINDOWS.X64_213000_db_home) (SID_NAME = ORCLPDB))) And added an alias in **tnsnames.ora**: LISTENER_2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522)) (CONNECT_DATA = (SERVICE_NAME = ORCLPDB.AD11.SIEMENS.NET))) When I execute "lsnrctl status LISTENER_2", I get: STATUS of the LISTENER ------------------------ Alias LISTENER_2 Version TNSLSNR for 64-bit Windows: Version 21.0.0.0.0 - Production Start Date 05-DEC-2024 15:18:31 Uptime 0 days 0 hr. 1 min. 0 sec Trace Level admin Security ON: Local OS Authentication SNMP OFF Listener Parameter File C:\Oracle\OraDB21c\WINDOWS.X64_213000_db_home\network\admin\listener.ora Listener Log File C:\Oracle\OraDB21c\diag\tnslsnr\\listener_2\alert\log.xml Listener Trace File C:\Oracle\OraDB21c\diag\tnslsnr\\listener_2\trace\ora_23584_28284.trc Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=.ad11.siemens.net)(PORT=5500))(Security=(my_wallet_directory=C:\ORACLE\ORADB21C\admin\orcl\xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "75b0d2bd17c4485c998c967244b4515d.ad11.siemens.net" has 1 instance(s). Instance "orcl", status READY, has 2 handler(s) for this service... Service "ORCLPDB.AD11.SIEMENS.NET" has 2 instance(s). Instance "ORCLPDB", status UNKNOWN, has 1 handler(s) for this service... Instance "orcl", status READY, has 2 handler(s) for this service... Service "bc74b627f47a416ab26784e660b5c7b2.ad11.siemens.net" has 1 instance(s). Instance "orcl", status READY, has 2 handler(s) for this service... Service "orcl.ad11.siemens.net" has 1 instance(s). Instance "orcl", status READY, has 2 handler(s) for this service... Service "orclXDB.ad11.siemens.net" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully I noticed the status of ORCLPDB is UNKNOWN but I have no idea why - guess it might be something to consider as the source of the problem. Additionally, I added the following line to **sqlnet.ora**: SQLNET.AUTHENTICATION_SERVICES = (NTS) And I also granted my current windows user with the required privileges, and it's verified by the fact that: ALTER SESSION SET CONTAINER = ORCLPDB; SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE AUTHENTICATION_TYPE = 'EXTERNAL'; Returns my current windows user. I use the following command to connect: sqlplus /@LISTENER_2 And tried many other ways as well, but keep getting that ORA-01017. Does anyone have a clue what is the problem here and how can I fix it?

I have SQL Server 2017 installed as the default instance hosted on a virtual server (GoDaddy) running Windows Server 2019 (this is a live production server). SQL is configured for mixed-mode authentication, which works perfectly in all respects when I log in using SQL Server Authentication. Because it's a default instance, the name of the SQL instance is the same as the Server Name ("MyServerName" -- not actual name, but using it for this post). When I connect to the Database Engine via SSMS, selecting MyServerName as the server and SQL Server Authentication with a SQL account (sysadmin), I can create a new Login using Windows authentication for user: MyServerName\adminusername. That Login shows up (as expected) under Security/Logins as "MyServerName\adminusername". I can assign this new Windows authentication user to the Server Role sysadmin, and also establish that new user as db Owner for any database (as long as I'm logged in to SQL Server using SQL Authentication). All of this is competely normal behavior. But here's where it gets weird: If I then log in to Windows on this same server (MyServerName) using that same "adminusername" user (who is a Windows Administrator on the server), then try to connect to the same instance of SQL server (MyServerName) via SSMS using Windows Authentication mode and the same user (MyServerName\adminusername), I get the following error: > The login is from an untrusted domain and cannot be used with > Integrated authentication. (Microsoft SQL Server, Error: 18452) Just to reiterate: - This problem is all within a single Windows server and a single instance of SQL Server (not trying to connect between two different servers). - The name of the Windows Server is MyServerName. - The server is not configured as a Domain Controller (it never has been). - The name of the SQL Server instance (default instance) is MyServerName. - I have no trouble when logging in to SQL server with SQL authentication. - I have no trouble logging into Windows Server as MyServerName\adminusername. - Even though I can create a Windows Authentication login as "MyServerName\adminusername", and "MyServerName" is both the Windows server name and the name of the SQL Server instance (default), it seems to think there is a mismatch between the "domain" (MyServerName) for the Windows authentication Login and the instance of SQL Server itself. I've been developing databases and associated web applications in SQL Server for over 15 years and have never seen this issue before. I've been all over the internet trying to find someone describing a similar issue, but so far no luck (other answers involve connections between multiple servers or SQL instances, or other very specific circumstances that do not apply to my situation). The only thing I can think of is that, when the virtual server was first created, GoDaddy set the server name as the IP address (replacing '.' with '-'). I changed the name of the server to its current name, but it's possible that I installed SQL Server when the Server name was still the original one assigned by GoDaddy, and then changed the server name after SQL installation. Even though the server has never been a Domain Controller, I've seen indications that SQL Server will need to be re-installed when switching to a Domain Controller. I wonder if the same is true if the Computer Name has been changed, even if is not a Domain Controller? To test this, I've tried reverting the computer name back to what the original computer name assigned by GoDaddy, but I continued to have the exact same issue. I'm tempted to try to uninstall SQL Server and re-install it again, but that would be days of work to re-configure all the different databases and associated websites/etc. I'm willing to do that if the probability of success is high, but at this stage I'm not confident enough that it will solve the issue in order to justify that amount of work and offline time of various websites. Incidentally, the reason I need to be able to fix this Windows Authentication login issue is that I need to set up replication (which uses Windows Login process accounts for various Agents). So "just use SQL authentication" isn't a viable work-around solution. ***EDIT***: I forgot to mention, whenever I attempt to log in to SQL server using Windows Authentication, the Server Manager records an Application log event for MSSQLSERVER that says: > SSPI handshake failed with error code 0x8009030c, state 14 while > establishing a connection with integrated security; the connection has > been closed. Reason: AcceptSecurityContext failed. The operating > system error code indicates the cause of failure. The logon attempt > failed. I doubt that provides anything not already evident from the full description above, but adding it for completeness.

As an example, imagine you have created a login for a local windows group, e.g. machine_name\group_name You rebuild the server (new physical machine) with the same machine name (machine_name), attach the old drives to it, including SQL server. The group has been recreated as well. But it is a new OS installation. As such, the new machine_name\group_name will have a new SID, and will not actually work. **Later on, in general, how can we detect a Windows login SID is no longer valid so we know we need to recreate the login?** SELECT SUSER_SID(machine_name\group_name) is still returning the old SID (as stored in sys.server_principals). [sys].[sp_validatelogins] doesn't report anything either. It seems that SUSER_SID looks at sys.server_principals first, so we're at a loss for how to detect this.

I have 4 Windows AD Domains and the same AD Group within them: - DEV\MyADGroup - SIT\MyADGroup - UAT\MyADGroup - PROD\MyADGroup The domains are separated from each other and not trusted, so we can prevent data/access spillage across domains. I also have 4 SQL instances, one for each domain. In a MS VS database solution, I'd like to do something like: CREATE LOGIN [$(USERDOMAIN)\MyADGroup] FROM WINDOWS GO but I get the error: > SQL70604: SqlCmd variable reference is not allowed in object names ($(USERDOMAIN)\MyADGroup). Is there some way to allow the MS VS project to understand that it should dynamically fill in the USERDOMAIN variable at Build-Time, so I can generate diff scripts and publish the solution easily to all 4 domains, without having to manually set the correct domain? Things I've tried: - the above, which throws the error - post-execution scripts, which breaks diff script generation and publishing Note: this quesion has nothing to do with database-level users, roles, permissions! We are talking about Windows AD Groups across different Windows AD domains, and how to map them to a SQL Server Instance-level Login!

I am trying to access LocalDB 2012 on my Windows 7 SP1 PC. I have successfully installed LocalDB and started the instance using the command line, and it is currently running. However, when I attempt to connect to this LocalDB instance using SQL Server Management Studio (SSMS), I receive the following error. > A network-related or instance-specific error occurred while > establishing a connection to SQL Server. The server was not > found or was not accessible. Verify that the instance name > is correct and that SQL Server is configured to allow remote > connections. > (provider: SQL Network Interfaces, error: 26 - > Error Locating Server/Instance Specified) > (.Net SqlClient Data Provider) I have verified the following configurations, and they seem to be working correctly as mentioned in the Link - TCP port is enabled. - SQL Server Configuration Manager is installed. - SQL Browser service is running. - Port 1433 is open. Also note that I have tried connecting using the following instance names, but without success: I have attempted to log in using Windows Authentication. (localdb)\v11.0 (localdb)\mssqllocaldb Given all of this, what could be the cause of the issue.? How can I solve this issue?

I have installed SQL Server Express 2019 on my machine and after installation, I am able to login into server using the Windows Authentication method like And as you can see the sa account has been disabled by default So I tried to enable the sa account like But I am getting this error message > Cannot alter the login 'sa', because it does not exist or you do not have permission.(Microsoft SQL Server, Error:15151) Even when I tried to change the server authentication mode from Windows Authentication mode to SQL Server and Windows Authentication mode at I am facing this error! > The EXECUTE permission was denied on the object 'xp_instance_regwrite', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error; 229) So it looks like I have no/not granted any permission to do any anything! when login with Windows which apparently is my only option to get into server!(this is also happening when I tried to create new user!) Can you please let me know what I am doing wrong and how I can handle this to act as an administrator for DB when I login? I have already seen and tried this post , so please kindly, if you are not sure about the solution do not close, down vote or redirect me again to this post?

SQLSERVERABC is on domain abc.local. If I connect to SQLSERVERABC via remote desktop, I cannot login to the database via SSMS on the local machine that I have remoted to. At SSMS login screen:

Server Type: Database Engine
Server Name: SQLSERVERABC
Authentication: Windows Authentication.
User name: abc\mylogin

The login attempt will timeout, and the server will not authenticate me or _anyone else_ via Active Directory on our domain controller. The weird thing is Windows Authentication works if I login via SSMS my local computer and not remoted into the SQL server via remote desktop. But we have remote users who are not on our domain (working from home) they have to remote to the SQL Server and can login using Windows Authentication with their domain credentials but only if they substitute the Server Name: SQLSERVERABC with its IP Address on the SSMS login QUI. SQLSERVERABC is set to both windows and SQL server authentication, All DNS instances are set up properly...I can not figure out why Windows Authentication will not work when user remote to the actual SQL box unless we use the box's IP address as its Server Name. Yes it's a work around but need fix it... Trying to get SQL server 2016 that is on a .local domain to allow windows authentication using it's SERVER NAME and not IP address

we have an issue connecting remotely to SQL Server via SSMS with Azure AD users. Any attempt to log in to a SQL Server instance in AWS with Windows Authentication throws an SSPI error. We've tried the Kerberos Configuration Manager to diagnose the root cause but that throws an error every time. The only solutions we've found are connecting to our old VPN before SQL login (which is not a viable long-term solution) or running SSMS as a different user and using our domain user. Since we're on 2016. Popup error message:

Cannot connect to x.x.x.x.
Additional information:
The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server)

Error message details:

===================================

Cannot connect to x.x.x.x.

===================================

The target principal name is incorrect.  Cannot generate SSPI context. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476 

------------------------------
Server Name: x.x.x.x
Error Number: 0
Severity: 11
State: 0
Procedure: GenClientContext


------------------------------
Program Location:

   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

Please not we were able to connect to the sql user with an old vpn but with aws VPN. Any insights and solution higly appreciated. We tried to solve using below documentation and no luck so far https://dba.stackexchange.com/questions/241051/the-target-principal-name-is-incorrect-cannot-generate-sspi-context-sql-or-ad

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 changed sa and Windows administrators password successfully, I can login to server with sa without any problem, services working normally, database is not corrupted and working without any problem. Problem is I cannot login with Windows authentication after changing sa password, mixed login mode is enabled, even I can create new user and I can login with it but I can not login with Windows authentication, the error I get is always:

Number: 18456 Severity: 14 State: 1 Line Number: 65536

Another thing is I can connect to Analysis services with Windows authentication, but Database Engine does not accept Windows authentication. info:

@@Version

: > Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor) SSMS is checked to run as Administrator always, but does not help.

I maintain a SQL Server database that's used as a workflow tool for a team of a few dozen users. Those users interact with the database via an MS Access front-end and Windows Authentication. Part of the process requires that anyone in the firm (>10,000 people) needs to be able to submit jobs and feedback via Excel forms that are available to all. To accommodate this, we use a SQL Authenticated service account that's permissioned to write to the required target tables only. It uses a connection script that looks something like this:

.Open "Provider=SQLOLEDB; SERVER=[ServerAddress]; DATABASE=[DatbaseName]", [ServiceAccountName], [ServiceAccountPassword]

This has been working fine for many years. Now however, IT wants to prohibit the use of service accounts, and rely on Windows authentication exclusively. They have asked me to decommission the service account, and I'm trying to figure out how I can accomplish this. My first thought was that I should be able to use the guest user account in lieu of a service account. I made sure the guest account was active in the database, gave it Insert permissions to the target tables, and changed the connection script to this:

.Open "Provider=SQLOLEDB; SERVER=[ServerAddress]; DATABASE=[DatbaseName]; Trusted_Connection=yes"

This works when I use it, but fails for any users who don't have Windows Authenticated logins in the database. The error they receive reads "Login failed for [user]". So, my basic question would be, is what I'm trying to accomplish feasible? Can the guest account be used in lieu of a SQL Authenticated service account? If so, am I missing something obvious here? If not, is there a preferable method for allowing submissions by users who don't have login privileges to the database? Thanks for reading. Any suggestions would be greatly appreciated. EDIT: To clarify the goal here, I need to be able to allow any member of the firm to submit a job record. I cannot assume any knowledge of these users, including what Active Directories they may belong to. Basically, I need an "other" category for any users who are not recognized by the database. I was hoping the guest account might be a means of accomplishing this.

I ran into an issue today where the User Principal Name was greater than 20 characters in length, so the samAccountName was capped at 20. This resulted in SQL Server not finding the user when the UPN was typed in, even in Domain\Username format. Googling has led me to several pages from 2014 or older, and even a Q&A on this forum. A detailed explanation may have been on Microsoft Connect, but it's retired and no longer available Can someone confirm that even SQL Server 2016 and beyond (or Azure) still only support samAccountNames?

Is it possible to create a Windows User in the database when the domain is not accessible? I am preparing database at my dev environment outside of the targeted domain. Created user is not expected to work at my dev of course. I am trying: CREATE USER [NOTEXISTINGDOMAIN\User1] WITHOUT LOGIN; But getting an error: Windows NT user or group 'NOTEXISTINGDOMAIN\User1' not found. Check the name again. I have searched for help and it seems there is no way...which I don't believe much cause this seems to me like relevant scenario. **ADDITION:** Let me explain my scenario. My task is to downgrade database to older MS SQL Server (SQL 2017 to SQL 2016 to be precise). I cannot do this in customers environment (and domain, DC) cause obviosly there is no both MS SQL versions available. So I amd doing the downgrade in my dev. And I want to prepare the database for my customer with all objects in it, including users of all types, including Windows Users - so when my customer restores the database everything is ready and the same as in original database. Obviosly I'll have to create users later after restoring the database in the customers environment.

we have two SQL Server 2017 "ServerA" and "ServerB" on Windows Server 2016 in the same domain. Both SQL Server have a Login defined "mydomain\dbaccount". On ServerA we have a Login defined "mydomain\testaccount" which can access the Database. We want to connect with the "mydomain\testaccount" to ServerB via Linked Server. The linked server should connect with the "mydomain\dbaccount" via Windows Authentication. We cannot provide password in linked server of "mydomain\dbaccount" as we want to connect via Windows Authentication. And the Login on ServerB is defined as Windows Authentication. We tried to setup the Linked Server with the local login as "mydomain\testaccount" and impersonate=true and we are getting the following error: > The OLE DB provider "SQLNCLI11" for linked server "ServerB" reported > an error. Authentication failed. Cannot initialize the data source > object of OLE DB provider "SQLNCLI11" for linked server "ServerB". OLE > DB provider "SQLNCLI11" for linked server "ServerB" returned message > "Invalid authorization specification". (Microsoft SQL Server, Error: > 7399) We tried to setup the Linked Server with the local login as "mydomain\testaccount" and impersonate=false and remote user as "mydomain\dbaccount" and we are getting the following error: > Login failed for user 'mydomain\dbaccount'. (Microsoft SQL Server, > Error: 18456) We tried to setup the Linked Server with the local login as "mydomain\testaccount" and impersonate=true and without remote user and in options we checked "Be made using this security context" and defined remote login as "mydomain\dbaccount" without password and we are getting the following error: > Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL > Server, Error: 18456) How can we setup Linked Server with remote Windows Authentication?