Skip to content

Latest commit

 

History

History
167 lines (113 loc) · 11.3 KB

create-database-scoped-credential-transact-sql.md

File metadata and controls

167 lines (113 loc) · 11.3 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
VanMSFT
vanto
03/15/2023
sql
t-sql
reference
DATABASE SCOPED CREDENTIAL
DATABASE_SCOPED_CREDENTIAL_TSQL
SCOPED_TSQL
CREATE_DATABASE_SCOPED_CREDENTIAL
CREATE_DATABASE_SCOPED_CREDENTIAL_TSQL
SCOPED_CREDENTIAL_TSQL
SCOPED_CREDENTIAL
DATABASE SCOPED CREDENTIAL statement
credentials [SQL Server], DATABASE SCOPED CREDENTIAL statement
TSQL
=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=aps-pdw-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw]

Creates a database credential. A database credential is not mapped to a server login or database user. The credential is used by the database to access to the external location anytime the database is performing an operation that requires access.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

CREATE DATABASE SCOPED CREDENTIAL credential_name
WITH IDENTITY = 'identity_name'
    [ , SECRET = 'secret' ]

[!INCLUDEsql-server-tsql-previous-offline-documentation]

Arguments

credential_name

Specifies the name of the database scoped credential being created. credential_name cannot start with the number (#) sign. System credentials start with ##.

IDENTITY ='identity_name'

Specifies the name of the account to be used when connecting outside the server.

  • To import a file from Azure Blob Storage or Azure Data Lake Storage using a shared key, the identity name must be SHARED ACCESS SIGNATURE. For more information about shared access signatures, see Using Shared Access Signatures (SAS). Only use IDENTITY = SHARED ACCESS SIGNATURE for a shared access signature.
  • To import a file from Azure Blob Storage using a managed identity, the identity name must be MANAGED IDENTITY.
  • When using Kerberos (Windows Active Directory or MIT KDC) do not use the domain name in the IDENTITY argument. It should just be the account name.
  • In a SQL Server instance, if creating a database scoped credential with a Storage Access Key used as the SECRET, IDENTITY is ignored.
  • WITH IDENTITY is not required if the container in Azure Blob storage is enabled for anonymous access. For an example querying Azure Blob storage, see Importing into a table from a file stored on Azure Blob storage.

Important

The only PolyBase external data source that supports Kerberos authentication is Hadoop. All other external data sources (SQL Server, Oracle, Teradata, MongoDB, generic ODBC) only support Basic Authentication.

  • To load data into Azure Synapse Analytics, any valid value can be used for IDENTITY.
  • In an Azure Synapse Analytics serverless SQL pool, database-scoped credentials can specify workspace Managed Identity, service principal name, or shared access signature (SAS) token. Access is also possible via user identity, also known as "Azure AD pass-through" is possible in the databased-scoped credential, as is anonymous access to publicly available storage. For more information, see Supported storage authorization types.
  • In an Azure Synapse Analytics dedicated SQL pool, database-scoped credentials can specify shared access signature (SAS) token, custom application identity, workspace Managed Identity, or storage access key.

SECRET ='secret'

Specifies the secret required for outgoing authentication. SECRET is required to import a file from Azure Blob storage. To load from Azure Blob storage into Azure Synapse Analytics or Parallel Data Warehouse, the Secret must be the Azure Storage Key.

Warning

The SAS key value might begin with a '?' (question mark). When you use the SAS key, you must remove the leading '?'. Otherwise your efforts might be blocked.

Remarks

A database scoped credential is a record that contains the authentication information that is required to connect to a resource outside [!INCLUDEssNoVersion]. Most credentials include a Windows user and password.

Before creating a database scoped credential, the database must have a master key to protect the credential. For more information, see CREATE MASTER KEY (Transact-SQL).

When IDENTITY is a Windows user, the secret can be the password. The secret is encrypted using the service master key. If the service master key is regenerated, the secret is re-encrypted using the new service master key.

When granting permissions for a shared access signatures (SAS) for use with a PolyBase external table, select both Container and Object as allowed resource types. If not granted, you may receive error 16535 or 16561 when attempting to access the external table.

Information about database scoped credentials is visible in the sys.database_scoped_credentials catalog view.

Here are some applications of database scoped credentials:

Permissions

Requires CONTROL permission on the database.

SQL Server 2022

Starting in [!INCLUDEsssql22-md] a new type of connector was introduced, using REST-API calls replacing HADOOP. For Azure Blob Storage and Azure Data Lake Gen 2 the only supported authentication method is SHARED ACCESS SIGNATURE.

Please refer to create external data source for more information.

Examples

A. Creating a database scoped credential for your application

The following example creates the database scoped credential called AppCred. The database scoped credential contains the Windows user Mary5 and a password.

-- Create a db master key if one does not already exist, using your own password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<EnterStrongPasswordHere>';

-- Create a database scoped credential.
CREATE DATABASE SCOPED CREDENTIAL AppCred WITH IDENTITY = 'Mary5',
    SECRET = '<EnterStrongPasswordHere>';

B. Creating a database scoped credential for a shared access signature

The following example creates a database scoped credential that can be used to create an external data source, which can do bulk operations, such as BULK INSERT and OPENROWSET. Shared Access Signatures cannot be used with PolyBase in SQL Server, APS or Azure Synapse Analytics.

-- Create a db master key if one does not already exist, using your own password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<EnterStrongPasswordHere>';

-- Create a database scoped credential.
CREATE DATABASE SCOPED CREDENTIAL MyCredentials
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'QLYMgmSXMklt%2FI1U6DcVrQixnlU5Sgbtk1qDRakUBGs%3D';

C. Creating a database scoped credential for PolyBase Connectivity to Azure Data Lake Store

The following example creates a database scoped credential that can be used to create an external data source, which can be used by PolyBase in [!INCLUDEssazuresynapse-md].

Azure Data Lake Store uses an Azure Active Directory Application for Service to Service Authentication. Please create an Azure AD application and document your client_id, OAuth_2.0_Token_EndPoint, and Key before you try to create a database scoped credential.

-- Create a db master key if one does not already exist, using your own password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<EnterStrongPasswordHere>';

-- Create a database scoped credential.
CREATE DATABASE SCOPED CREDENTIAL ADL_User
WITH
    IDENTITY = '<client_id>@<OAuth_2.0_Token_EndPoint>',
    SECRET = '<key>'
;

More information