June 11, 2024 at 7:10 pm
I am VERY confused.
We are switching an application from using a SQL login to using a windows login that is part of a windows security group. So the group has db_datareader, db_datawriter, and execute on schema dbo. There are a couple of db_denydatawriters on other groups, but this proc is not writing to any tables. It's setting variables, calling @@VERSION, and converting datatypes before returning the result.
There is a stored procedure being called that checks the version of the program a user has, run by a website. Under the SQL login, everything worked fine. Under the windows login, we're getting "The EXECUTE permission was denied on the object..."
I have literally never seen this issue before. The account is a member of the Windows Security Group, the WSG is not orphaned, I double-checked the dbo schema and there is indeed EXECUTE granted on the whole schema and the proc belongs to the dbo schema.
I can run "EXECUTE AS LOGIN = 'Mylogin'" and the proc runs in SSMS. But when the application calls the proc, they are getting the execute permission denied. And they are positive the account they are using is the same one I'm running with EXECUTE AS.
Any thoughts on this one?
June 11, 2024 at 7:29 pm
It might be a Kerberos double hop problem where it falls back to, and manages to connect with, NT AUTHORITY\ANONYMOUS LOGON.
You could try checking the SPNs with the Kerberos Configuration Manager for SQL Server.
June 11, 2024 at 7:34 pm
Will that tell me if it's double-hopping?
June 11, 2024 at 7:41 pm
No, but it will tell you if the SQL Server SPNs are configured correctly. It also allows you to generate a script, to give to your domain admins, if they are not configured correctly.
You might also want to track logins with something like:
CREATE EVENT SESSION [Track_Login] ON SERVER
ADD EVENT sqlserver.login
(
ACTION (
sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.username
)
WHERE (
sqlserver.username <> 'NT SERVICE\SQLSERVERAGENT'
AND sqlserver.username <> 'NT SERVICE\SQLTELEMETRY'
AND sqlserver.username <> 'NT Service\MSSQLSERVER'
)
)
ADD TARGET package0.event_file(SET filename=N'C:\Tmp\Logons.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
GO
/*
WITH Logons
AS
(
SELECT timestamp_utc
,CAST(event_data AS xml) AS xEventData
FROM sys.fn_xe_file_target_read_file('C:\Tmp\Log*.xel', NULL, NULL, NULL)
)
SELECT timestamp_utc
,xEventData.value('(/event/action[@name=''username'']/value)[1]','sysname') AS username
,xEventData.value('(/event/action[@name=''database_name'']/value)[1]','sysname') AS database_name
,xEventData.value('(/event/action[@name=''client_hostname'']/value)[1]','sysname') AS client_hostname
,xEventData.value('(/event/action[@name=''client_app_name'']/value)[1]','sysname') AS client_app_name
FROM Logons
ORDER BY timestamp_utc;
*/
June 13, 2024 at 2:21 pm
Oh, thank you for that bit of code. I shall try it out. It might help.
June 13, 2024 at 3:47 pm
You can get a large file very quickly. In SSMS I would go Management | Extended Events | Sessions and right click on Track_Login and start it. Let it run for a couple of minutes and then stop it. Then run the query in the comments to see what to add to the filter in the event. Then re-create the event and run it for a bit longer etc.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply