Product:

Microsoft SQL server 2016

Issue:

Is there someone inside the database?

Suggested Solution:

Run this SQL script to see who have logged in to the database:

select 

@@servername as [ServerName],

getdate() as RunTime, 

spid, DB_NAME(dbid) as DatabaseName, 

login_time, 

last_batch, 

hostname, 

program_name, 

nt_domain, 

nt_username, 

loginame 

From master..sysprocesses

 

To get more information, like kerberos, run this SQL script:

select c.auth_scheme,* from sys.dm_exec_sessions s join sys.dm_exec_connections c on s.session_id=c.session_id where s.host_name ='servername'

To see active connections:

EXEC sp_who2 'active';

More Information:

https://www.sqlservercentral.com/articles/is-this-database-being-used

Product:

Microsoft SQL server 2016

Issue:

Error when testing linked servers in SQL server.

Solution:

Ensure the port is open first, by start PowerShell on the from server and use Test-NetworkConnection.  Enter below to check port 1433;

tnc servername -port 1433

If firewall is open, and you get a error saying “anonymous login” then the kerberos delegation does not work. You need to check that your account (or the account that run the SQL agent service) is allowed to be delegated, check with this power-shell command;

Get-ADUser -Filter {SamAccountName -eq "YourAccountSamName"} -Properties msDS-AllowedToDelegateTo | Select-Object -ExpandProperty msDS-AllowedToDelegateTo

You must check that the SQL server machine, have in AD allow to delegate, you do this with this PowerShell command (for both servers);

Get-ADComputer ComputerName -Properties * | Format-List -Property *delegat*,msDS-AllowedToActOnBehalfOfOtherIdentity

TrustedForDelegation must be True.

Ask the Active Directory team to correct above.

 Get-ADServiceAccount -Filter {SamAccountName -eq "YourServiceAccuntNameHere"} -Properties * | Format-List -Property *delegat*

Above need to be used, if the account is a Service Account in AD.

https://docs.microsoft.com/en-us/powershell/module/activedirectory/get-adserviceaccount?view=windowsserver2022-ps

More information:

Microsoft Made an Easy Button for SPN and Double Hop Issues

Double-Hop Linked Servers

https://help.zaptechnology.com/zap%20cubexpress/currentversion/content/topics/HowTo/UsingSPN.htm

https://social.technet.microsoft.com/wiki/contents/articles/717.service-principal-names-spn-setspn-syntax.aspx

https://kb.parallels.com/en/124644

https://www.sqlshack.com/how-to-create-and-configure-a-linked-server-in-sql-server-management-studio/

https://www.mssqltips.com/sqlservertip/6083/understanding-sql-server-linked-servers/

https://www.c-sharpcorner.com/UploadFile/suthish_nair/linked-servers-in-sql-server-2008/

https://en.hackndo.com/constrained-unconstrained-delegation/

https://www.microsoft.com/en-us/download/details.aspx?id=39046&WT.mc_id=soc-n-%5bTR%5d-loc-%5bServices%5d-%5bfarukc%5d