July 3, 2024 at 2:11 pm
I am at a loss and could use a second pair of eyes.
We have a Server with database A and database B, a windows security group (MyDom\MyGroupName) which is created with dbo schema as its default in both databases, and a service account in this group. MyDom\MyGroupName has db_datareader in both databases. There are no deny permissions in any other groups / windows users or on any individual objects. The service account does not have a separate user created for it. Its access is only through the group.
In database A, there is a stored procedure that pulls data from local tables and one local view (ViewA). ViewA pulls data from ViewB, also local to database A. ViewB pulls data from 3 tables in database B.
Schema on all objects is "dbo" and dbo schema in both databases is owned by "dbo."
The application UI gets the error "The SELECT permission was denied on the object 'tablename', database 'Database B', schema 'dbo'." But the group exists in both databases (as mentioned above).
If I impersonate the service account with EXECUTE AS LOGIN, everything works fine. No issues. I don't know what the users are talking about. Permissions are GREATE.
If I impersonate the service account with EXECUTE AS USER, I get the error: "The server principal "MyDom\ServiceAcct" is not able to access the database "Database B" under the current security context.".
I have even used the SUSER_NAME(), USER_NAME() functions in my impersonate code to verify that the user executing the code in SSMS is MyDom\ServiceAcct and had the devs put it in their error logging to verify that no context switching is happening.
Cross database ownership chaining is off and cannot be turned on because it would violate our security policies. I dropped the group from database B and re-added it in hopes of fixing that random, sometimes corruption of logins but that didn't change anything.
What am I missing? What else do I need to look at to find out why this issue is occurring?
July 3, 2024 at 3:08 pm
If I impersonate the service account with EXECUTE AS LOGIN, everything works fine.
This makes sense. EXECUTE AS LOGIN allows instance wide permissions.
If I impersonate the service account with EXECUTE AS USER, I get the error: "The server principal "MyDom\ServiceAcct" is not able to access the database "Database B" under the current security context.".
This also makes sense. EXECUTE AS USER restricts the permissions to the database.
The application UI gets the error "The SELECT permission was denied on the object 'tablename', database 'Database B', schema 'dbo'." But the group exists in both databases (as mentioned above).
I suspect this is where the problem is. Try adding MyDom\ServiceAcct to Database B. ie It might be looking for MyDom\ServiceAcct, not MyDom\MyGroupName, in Database B.
It might be better to use stored procedures and module signing.
July 3, 2024 at 3:17 pm
They are using a stored procedure at the upper level which references the views, and I'm not sure changing the last view's code to be a proc in the other database would really fix the issue. Or even a proc in the current database reaching out to the second database. The service account just can't seem to get into the database.
I did just hear someone at work make a comment that since they were switching from a SQL login to a windows group that the problem might be in the connection string. They might be trying to connect as a "user" instead of a "login."
I'm not sure how that would work, but I've asked for the devs' connection strings to verify.
July 3, 2024 at 3:26 pm
If they are using a SP, I would be inclined to remove MyDom\MyGroupName from DatabaseB and use Module signing. In outline;
USE DatabaseA;
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE dbo.YourProc
AS
SET NOCOUNT, XACT_ABORT ON;
select * from DatabaseB.dbo.YourView2;
RETURN;
GO
IF EXISTS
(
SELECT 1
FROM sys.crypt_properties P
JOIN sys.certificates C
ON P.thumbprint = P.thumbprint
WHERE OBJECT_SCHEMA_NAME(P.major_id) = N'dbo'
AND OBJECT_NAME(P.major_id) = N'YourProc'
AND C.[name] = N'dbo_YourProc$Cert'
)
DROP SIGNATURE FROM dbo.YourProc BY CERTIFICATE dbo_YourProc$Cert;
IF EXISTS
(
SELECT 1
FROM sys.certificates
WHERE [name] = N'dbo_YourProc$Cert'
)
DROP CERTIFICATE dbo_YourProc$Cert;
GO
DECLARE @password nchar(37) = convert(nchar(36), newid()) + 'a'
,@SQL nvarchar(MAX);
SET @SQL = CONCAT
(
N'CREATE CERTIFICATE dbo_YourProc$Cert
ENCRYPTION BY PASSWORD = N''', @password, N'''
WITH SUBJECT = ''"Cert for dbo_YourProc"'';'
);
EXEC (@SQL);
SET @SQL = CONCAT
(
N'ADD SIGNATURE TO dbo.YourProc BY CERTIFICATE dbo_YourProc$Cert
WITH PASSWORD = N''', @password, N''';'
);
EXEC (@SQL);
ALTER CERTIFICATE dbo_YourProc$Cert REMOVE PRIVATE KEY;
GO
DECLARE @public_key varbinary(MAX) = CERTENCODED(CERT_ID(N'dbo_YourProc$Cert'))
,@SQL nvarchar(MAX);
USE DatabaseB;
DROP USER IF EXISTS dbo_YourProc$CertUser;
IF EXISTS
(
SELECT 1
FROM sys.certificates
WHERE [name] = N'dbo_YourProc$Cert'
)
DROP CERTIFICATE dbo_YourProc$Cert;
SET @SQL = CONCAT
(
N'CREATE CERTIFICATE dbo_YourProc$Cert
FROM BINARY = ', CONVERT(nvarchar(MAX), @public_key, 1), N';'
);
EXEC (@SQL);
GO
CREATE USER dbo_YourProc$CertUser FROM CERTIFICATE dbo_YourProc$Cert;
/*GRANT [rights] ON [object] TO dbo_YourProc$CertUser;*/GRANT SELECT ON dbo.YourView2 TO dbo_YourProc$CertUser;
GO
USE DatabaseA;
GO
July 3, 2024 at 3:48 pm
I don't see anything in their connection strings that indicate they're trying to log in as a "user" vs "login" so I still have no idea what the issue is.
July 3, 2024 at 3:54 pm
I think the problem might be it is looking for the user, not the group, in DatabaseB.
I am not anywhere I can test this.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply