We recently disabled a SQL account on one of our production servers at the request of the application owner, account no longer used apparently. Since disabling we have seen login failures in the SQL error log. See image below. The app team cannot locate the process using it and have pushed back to me, I cannot see anything in sys.sysprocesses sys.dm_exec_sessions sys.dm_exec_connections sp_whosisactive doesn't capture any code running either. I feel its almost a heartbeat process from the app server that checks every two minutes to make sure the server is up. Is there any other ways to capture the activity, profiler isn't a quick and dirty option as this server is very temperamental. We are digging into the logs on the app side as well.

getting error: "The target database ('MyDB') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online." in the SQL error logs with no information who is trying to log in. This is on SQL 2017 version 14.0.3026.27 with Always On High Availability

I have this situation. I created a logon script to block any session for specific logins. These logins are in a table. So the behaviour of my script is simple. Get the login from the table, compare it with the login who wants to access and then insert some information to another table. The question here is: I tested in one instance and everything went fine but in other instance and now in the previous instance when I enable the logon trigger appears a pop up and block my personal account. My account is not included in the table so should not have this behaviour. Obviously the part to insert date in a table, doesn't work. Also other logins have been blocked CREATE TRIGGER Accounts_V1 ON ALL SERVER WITH EXECUTE AS 'DBA' FOR LOGON AS BEGIN DECLARE @Logon as varchar(50) DECLARE @AppName as varchar(250) DECLARE @ComputerName as varchar(50) DECLARE @ServerName as varchar(50) DECLARE @Original_Login as varchar(50) SET @ComputerName = HOST_NAME () SET @ServerName = @@servername SET @AppName = APP_NAME() SET @Original_Login = ORIGINAL_LOGIN () SET @Logon = ( select LoginName FROM [dba]..[MissusesAccounts_test] where LoginName NOT like '%WIN%' and ( @AppName like '%Microsoft%' or @AppName like '%PowerShell%' ) and @ComputerName not like '%sql%') if (@Logon = @Original_Login) INSERT INTO [dba]..[LogonAccounts_test] ([ComputerName] ,[ServerName] ,[AppName] ,[Logon]) VALUES (@ComputerName ,@ServerName ,@AppName ,@Logon) BEGIN print 'This login: '+@Logon+' should not be used by individuals to run interactive queries in ' +@AppName+' , and email has been sent to the Development Manager to investigate' END END REVERT

**The Issue** ----- I have two SQL Server 2016 Standard Edition servers on the same domain. **ServerA** has two linked server connections setup to **ServerB**. The first linked server connection is using a dedicated remote **SQL Login** security context. That one always was and still is working fine. The second connection uses the "login's current security context" which is a **Windows Authentication (AD) Login** on ServerA. This was also working fine up until today. Today out of nowhere, everyone who connects to ServerA and then tries to run a query that uses the second linked server connection, ends up getting hit with this error: > OLE DB provider "SQLNCLI11" for linked server "LinkedServerName" > returned message "Cannot generate SSPI context". Msg -2146893044, > Level 16, State 1, Line 0 SQL Server Network Interfaces: The logon > attempt failed Also the users are able to directly connect to both ServerA and ServerB fine currently, and even execute queries against objects on ServerB directly, but when they try to run queries referencing the same objects on ServerB via the linked server on ServerA is the only time they get the above error, currently. ----- **Relevant Information** ----- This is what we currently see for **SPNs** and logs (sorry for all the obfuscation): **ServerB Successful SPN Registration SQL Logs:** **ServerB Successful SPN Registration SQL Logs:** - Notice both servers are successfully registering SPNs for themselves for both port 1433 and no port specified. **ServerA Registered SPNs:** - I'm not sure what the second "CN=" line (second to last line) is but it literally says "CN=ServerASvc", but that's not the name of the SQL Service account. The SQL Service Account is "SQLServiceAccount1". - I'm also not sure why the last line has an SPN for just the server name "ServerA" (which we do have a Host / CName for in our DC), but seems redundant to the one of the previous lines. **ServerB Registered SPNs:** To recap my SPNs: - My ServerA's SQL Server Instance's Service Account (let's call it ServiceAccountA) has an SPN to trust its own server. - My ServerB's SQL Server Instance's Service Account (ServiceAccountB) has an SPN to trust its own server. - Delegation is setup for ServiceAccountA to be able to access the SPN for ServiceAccountB. **ServerA's** service account is **SQLServiceAccount1** and is also the same service account used on our DevSqlServer1. **ServerB's** service account is **SQLServiceAccount2** and is also the same service account used on our DevSqlServer2. Running the query SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID returns TCP and KERBEROS on both **ServerA** and **ServerB** for me. Also, different SQL Server Instance Service Accounts are running for each server. **ServiceAccountA** for ServerA, and **ServiceAccountB** for ServerB. The night before this issue started happening, Windows updates were installed on ServerB (and a few of our other servers). Specifically this update [November 10, 2020—KB4586830 (OS Build 14393.4046) ](https://support.microsoft.com/en-us/topic/november-10-2020-kb4586830-os-build-14393-4046-cc8a2d8c-4af6-576d-4f32-a3315bdb396e) which apparently states this: > After installing this update on domain controllers (DCs) and read-only > domain controllers (RODCs) in your environment, you might encounter > Kerberos authentication and ticket renewal issues. This is caused by > an issue in how CVE-2020-17049 was addressed in these updates. We tried rolling back the update, which invoked a restart of the server and things seemed to start working again, but then started breaking again for just our web server (a third / separate server), which hits our ServerA to query the linked server to ServerB. Originally ServerA and ServerB were part of separate Windows Failover Clusters (ServerA1 & ServerA2, and ServerB1 & ServerB2). When that was the case, this [DBA.StackExchange answer](https://dba.stackexchange.com/a/278780/150011) by Hannah Vernon sounded similar to what we experienced. The only difference is we previously weren't letting the Service Accounts register / deregister the SPNs, when the issue started. Now we are letting them do so, and there haven't been any failover events, and we triple checked all SPNs are setup correctly currently. So I don't think that's our issue here. At this point in time, both clusters are destroyed, and the secondary servers removed, yet the issue still persists with the individual servers (ServerA and ServerB). ----- **Things We've Tried / Issue Progression** ----- Logging out and logging back in after changing my AD password did **not** fix the issue, but physically restarting my machine **temporarily** did fix it for just me. I ran setspn -l ServerA_SQLServiceAccountName and after I did that, I did see the SPN that was in my **SQL Error Log**. So I think [Pinal Dave was right](https://blog.sqlauthority.com/2015/12/03/sql-server-could-not-register-service-principal-name-persists-notes-from-the-field-105/) in my case. Today I woke up and everyone on my domain is hosed again, myself included. I tried flushing the DNS on ServerA, and now we're receiving the error "*Linked Server error: Login Failed for user 'NT AUTHORITY\ANONYMOUS LOGON'*" instead of my original error, when querying the linked server from ServerA. It appears I am facing some sort of SPN / double-hop issue because when I RDP directly to ServerA and connect to my SQL Instance on ServerA (from within the RDP session - same account as I'm connecting to the instance when not RDP'ed in) and try querying the linked server, everything is all good. The question is, are my SPNs actually correct? (See current SPNs in the "**Relevant Information"** section above.) We triple checked the SPNs, and the AD delegation properties of the Service Accounts. We're fairly confident all ducks are in order. We're fully down again: web server, SSMS, etc, no matter how we try to query the linked server, which I think is due to the default 10 hour ticket time of Kerberos lapsing, and it not correctly renewing a new instance of the same ticket. We're currently facing this error: > Msg 18456, Level 14, State 1, Line 1 Login failed for user 'NT > AUTHORITY\ANONYMOUS LOGON'. We've been trying to troubleshoot using the **Kerberos Configuration Manager** and the only issue it found (this entire time) is a warning saying "*Kerberos Configuration Manager reports that "TCP must be enabled to use Kerberos Authentication*": We've checked TCP is enabled in both of our servers, so I'm not sure why it's saying this warning or how to troubleshoot it, and if it's our root issue or not. **Another thing worth noting** is we've only been intermittently down for most of the time. That is, some users are able to successfully query the linked server when using our applications (web, mobile, etc) and some aren't. So if it was a true SPN issue, I would think no one would be able to query the linked server - an *all or nothing* situation. This definitely feels more like a random Kerberos issue (possibly related to the Windows Update mentioned earlier). At this point, we've tried purge on our KLIST Kerberos tickets, ipconfig /flushdns both on the client machine and servers (SQL Servers, DNS Servers, and Web Server), and have tried restarting the client machines and server machines, rolling back the aforementioned Windows Update on whatever machines we were reliably sure had it, in addition to everything else previously mentioned, to no avail. At best we've gotten very intermittent mixed results with sometimes restarting the client machine sort of works, temporarily. ----- **Current Situation** ----- Currently everything is truly a single server for ServerA and a single server for ServerB (these were Windows Failover Clusters at the start of this issue, but have seen been removed and the clusters destroyed). We also have dev copies of ServerA (**ServerADev**) and ServerB (**ServerBDev**) which share the same SQL Service accounts. We've since setup replication from ServerB to ServerA to side-step the issue in production for now, but we encountered the same issue on our dev environment this week as well. I tried using xp_cmdshell to run klist purge and I cycled the SQL Services for both ServerADev and ServerBDev, but the issue still persisted. At this point, I'm pretty sure the issue is due to the botched Windows update [November 10, 2020—KB4586830 (OS Build 14393.4046)](https://support.microsoft.com/en-us/topic/november-10-2020-kb4586830-os-build-14393-4046-cc8a2d8c-4af6-576d-4f32-a3315bdb396e) that specifically states "*you might encounter Kerberos authentication and ticket renewal issues*". Unfortunately this was installed on a multitude of our servers (both SQL Servers and Domain Controllers) and subsequent Windows updates that have ran, remove the botched update from the **Windows Update History**, so I have no reliable way to tell which servers even originally received the botched update.

I have a dev team who have a number of SQL databases used as a back end for mostly excel front end. This originally came from an excel spreadsheet until it got made into SQL databases. At first, they were not too concerned about security and decided to give access to a domain group logon that contains basically all the organisation's users. Over time they have put more sensitive information into the databases and have realised that anyone in the organisation can view the information if they were to use another tool, power BI or SSMS due to the domain logon. They say that they still need to use the domain logon as everyone needs access but wants to secure the sensitive information on certain tables but still be accessible to their queries from excel. They have suggested Signing Stored Procedures with a Certificate but I am concerned they will want to try this with all their code placed in SPs, removing the domain logon, and this would create numerous amounts of admin to maintain all of them. How am I able to implement this in SQL Server in another way?

I've to implement the following requirement: Access to SQL Server instance shall be allowed only from a C# application. Users shall not be able to access any database (even those in which they have access) via SQLCMD, SSMS. Access using SSMS shall be allowed only for logins that are sysadmin, serveradmin. Connection from one specific host machine shall be denied. Connections from a specific C# application shall be allowed. Is it possible, secure and safe to implement this via logon trigger? If yes, how can I implement it in a reliable manner. In order to apply the logon trigger, is it necessary to be done via SSMS, only once to be activated. In case something goes wrong how can I disable it?

I have this logon trigger to restrict IP addresses. It is the same trigger I have seen everywhere. However, it is not working as expected, and I was hoping someone could give some direction. I am able to connect to this account / server from SQL*Plus and from a PHP script on a server where the IP is not in the list of IP's.

CREATE OR REPLACE TRIGGER check_ip_addresses_trg
AFTER LOGON
ON DATABASE

BEGIN
  IF USER IN ('JSMITH') THEN
     IF SYS_CONTEXT('USERENV', 'IP_ADDRESS') NOT IN
       ('192.168.170.34',
        '192.168.170.39',
        '192.168.170.57',
        '192.168.170.62') THEN
       RAISE_APPLICATION_ERROR(-20001, 'Can not log in from this IP address (' || SYS_CONTEXT('USERENV', 'IP_ADDRESS') || ')');
     END IF;
 END IF;

But, if I try from a different server: sqlplus jsmith/password@dbserver.dbase.com, I am able to log into SQL*Plus successfully. From this one PHP script I wrote, I also try to connect to this account, and I am able to connect successfully. We are running SE 11g R2. I had expected an error message. Any thoughts?

Typically when an oracle object goes invalid due to a dependent object being modified, the next execution of the invalid object will compile it and everything will be execute as expected (assuming no errors caused by the modification). This works as expected for standard table triggers: -- connect to DB connect myschema@TESTDB -- Create table create table myschema.t as select * from all_objects; -- Create before insert create or replace trigger myschema.trg before insert on t for each row begin :new.object_id :=12; end; / -- Grant insert privilege to table grant insert on myschema.t to otherschema; -- Modify underlying table to make trigger go invalid alter table myschema.t drop column owner; -- Connect as otherschema connect otherschema@TESTDB -- View status of trigger (invalid) select object_name, status from dba_objects where owner = 'MYSCHEMA'; OBJECT_NAME STATUS ----------- ------- TRG INVALID -- insert record into myschema.t insert into myschema.t select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID, DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME, TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY, NAMESPACE,EDITION_NAME from all_objects where rownum=1; -- View status of trigger (valid) select object_name, status from dba_objects where owner = 'MYSCHEMA'; OBJECT_NAME STATUS ----------- ------- TRG VALID All of that is as expected... now lets look at a logon trigger: -- Connect as privileged schema connect mydba@TESTDB -- Create table in privileged schema create table mydba.test_table ( c1 varchar2(100), c2 date ); -- Create logon trigger create or replace trigger mydba.test_logon_trg after logon on database declare v_variable varchar2(100); begin select c1 into v_variable from mydba.test_table; exception when no_data_found then null; end; / -- Modify table to make trigger go invalid alter table mydba.test_table modify ( c1 varchar2(200) ); -- Check status of trigger ( invalid ) select owner, object_name, status from dba_objects where owner = 'MYDBA' and object_name = 'TEST_LOGON_TRG'; -- Connect as non-privileged user (if you use a privileged, i.e. DBA, account it bypasses the trigger) connect myschema@TESTDB ORA-04045: errors during recompilation/revalidation of MYDBA.TEST_LOGON_TRG ORA-01031: insufficient privileges It looks like the database tries to compile the invalid trigger as expected, but it throws a privilege warning. The MYDBA schema has the DBA privilege and so it should have all the privileges necessary.

I wanted to create a trigger to record all the names of logins(who logon into system) in ServerLogonRecords table(all the columns are nullable) using LOGON trigger. I get information using eventdata() system function and convert the result into nvarchar type. create trigger tr_recorder on all server for logon as begin declare @var nvarchar(70) set @var = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)', 'nvarchar(70)') insert into ServerLogonRecords values(@var) end Now I can't even login using my privileged account because all logins fail. I want to know why do logins fail (is my trigger doing something wrong?). And any suggestions how to fix this in order to be able to login again into the instance will be very appreciated. Thanks.