How can I connect ADFS to a SQL database that's on a different domain?

Kalshin 1 Reputation point
2020-07-27T22:18:34.167+00:00

Hello.
We have 2 domains. The first one contains technical servers, database, etc., the second one contains supporting servers. They can't trust each other (company rules). When configuring ADFS in domain 2, I need to connect to a SQL 2016 database in domain 1. But the ADFS connection uses Windows Authentication and as a result I get an error.
I create scripts:
Export-AdfsDeploymentSQLScript -DestinationFolder "C:\SQLScript" –ServiceAccountName Domain2\ADFS
CreateDB.sql works fine, but Set-Permissions.sql completed with errors

Msg 15004, Level 16, State 1, Procedure sp_validname, Line 61, Name cannot be NULL.

I think this is because he didn't find a user with the same sid. Right?
In SQL script it is like this:

DECLARE hex_account_sid varbinary(85)
SET hex_account_sid - 0x010500000000000515000000EDF2C8FA8D26A855458E07EB50040000

DECLARE service_account sysname
SELECT service_account - SUSER_SNAME(hex_account_sid)

I thought it might be possible to create a user with the same sid
CREATE LOGIN adfs_service with password - , SID - 0x010500000000000515000000EDF2C8FA8D26A855458E07EB50040000;
but got an error

Msg 15419, Supplied parameter sid should be binary(16).

So my question are:

  1. How can I configure ADFS to use SQL authentication?
    or
  2. How can I create the correct permission?
Azure SQL Database
Active Directory Federation Services
Active Directory Federation Services
An Active Directory technology that provides single-sign-on functionality by securely sharing digital identity and entitlement rights across security and enterprise boundaries.
1,259 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. 9704244848 186 Reputation points
    2020-07-28T15:56:08.963+00:00
    1. How can I configure ADFS to use SQL authentication?

    I have not found any information that AD FS supports other authentication methods - only Integrated Security. In all (in)offical articles AD FS use the credentils for the database connection, which is configured for the AD FS windows service. In my opinion an active directory trust relationship is necessary.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.