Share via

how does database scoped credential work

Colin Ferguson 106 Reputation points
2024-06-19T13:08:07.25+00:00

I understand how to create and why, but how does then underlying process work?
how does the second server know about the credentials created on the first server when using polybase.
Our security team want an explanation as we wish to connect an on prem SQL box to a third party Azure Database.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,208 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 105.8K Reputation points MVP
    2024-06-19T21:27:35.9833333+00:00

    As far as I understand, SQL Server uses the stored credential when connecting to the other server. That is, the other server sees in it the connection string.

    Thus, the credential is stored in the database. It is encrypted and protected by the database master key. The documentation says that if there is a SECRET, it is protected by the service master key, but I'm wondering if that is correct. That may be a holdover from CREATE CREDENTIAL which creates a server-scoped credential.

    0 comments
  2. MikeyQiaoMSFT-0444 1,590 Reputation points
    2024-06-20T07:26:02.0933333+00:00

    Hi，Colin Ferguson

    Create database credentials through CREATE DATABASE SCOPED CREDENTIAL.

    Database scoped credentials are stored in the system catalog of SQL Server and are stored in an encrypted manner.

    When performing tasks, the PolyBase engine uses the credentials stored in the system to establish a connection with the external data source.

    This is because the credentials are bound together with the external data source definition. Then create the external data source through CREATE EXTERNAL DATA SOURCE and specify the credentials just created.

    Since the scoped credentials are stored in the system catalog of SQL Server, they can only be accessed with the appropriate permissions, ensuring the security of the credentials during transmission.

    When creating an external data source, different database drivers support different encryption options. Refer to CREATE EXTERNAL DATA SOURCE CONNECTION_OPTIONS.

    Best Regards,

    Mikey Qiao

    If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.

    0 comments