Събитие
Получете сертификат за Microsoft Fabric – безплатно!
19.11, 23 ч. - 10.12, 23 ч.
За ограничен период от време екипът на общността на Microsoft Fabric предлага безплатни ваучери за изпит DP-600.
Подгответе се сегаТози браузър вече не се поддържа.
Надстройте до Microsoft Edge, за да се възползвате от най-новите функции, актуализации на защитата и техническа поддръжка.
Applies to: SQL Server Azure SQL Managed Instance
Creates a server-level credential. A credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server. Most credentials include a Windows user and password. For example, saving a database backup to some location might require SQL Server to provide special credentials to access that location. For more information, see Credentials (Database Engine).
Бележка
To make the credential at the database-level use CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).
Create a server-level credential with CREATE CREDENTIAL
when you need to use the same credential for multiple databases on the server.
CREATE DATABASE SCOPED CREDENTIAL
to make the database more portable. When a database is moved to a new server, the database scoped credential will move with it.Transact-SQL syntax conventions
CREATE CREDENTIAL credential_name
WITH IDENTITY = 'identity_name'
[ , SECRET = 'secret' ]
[ FOR CRYPTOGRAPHIC PROVIDER cryptographic_provider_name ]
Specifies the name of the credential being created. credential_name cannot start with the number (#) sign. System credentials start with ##.
Важно
When using a shared access signature (SAS), this name must match the container path, start with https and must not contain a forward slash. See example D.
When used for backup/restore using a to external data platforms, such as Azure Blob Storage or S3-compatible platforms, the following table provides common paths:
External Data Source | Location path | Example |
---|---|---|
Azure Blob Storage (V2) | https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername> |
Example D. |
S3-compatible object storage | - S3-compatible storage: s3://<server_name>:<port>/ - AWS S3: s3://<bucket_name>.S3.<region>.amazonaws.com[:port]/<folder> or s3://s3.<region>.amazonaws.com[:port]/<bucket_name>/<folder> |
Example F. |
Specifies the name of the account to be used when connecting outside the server. When the credential is used to access the Azure Key Vault, the IDENTITY is the name of the key vault. See example C below. When the credential is using a shared access signature (SAS), the IDENTITY is SHARED ACCESS SIGNATURE. See example D below.
Важно
Azure SQL Database only supports Azure Key Vault and Shared Access Signature identities. Windows user identities are not supported.
Specifies the secret required for outgoing authentication.
When the credential is used to access Azure Key Vault, the SECRET argument must be formatted as a service principal's <client ID> (without hyphens) and <secret>, passed together without a space between them. See example C below. When the credential is using a shared access signature, the SECRET is the shared access signature token. See example D below. For information about creating a stored access policy and a shared access signature on an Azure container, see Lesson 1: Create a stored access policy and a shared access signature on an Azure container.
Specifies the name of an Enterprise Key Management Provider (EKM). For more information about Key Management, see Extensible Key Management (EKM).
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.
After creating a credential, you can map it to a SQL Server login by using CREATE LOGIN or ALTER LOGIN. A SQL Server login can be mapped to only one credential, but a single credential can be mapped to multiple SQL Server logins. For more information, see Credentials (Database Engine). A server-level credential can only be mapped to a login, not to a database user.
Information about credentials is visible in the sys.credentials catalog view.
If there is no login mapped credential for the provider, the credential mapped to SQL Server service account is used.
A login can have multiple credentials mapped to it as long as they are used with distinctive providers. There must be only one mapped credential per provider per login. The same credential can be mapped to other logins.
Requires ALTER ANY CREDENTIAL permission.
The following example creates the credential called AlterEgo
. The credential contains the Windows user Mary5
and a password.
CREATE CREDENTIAL AlterEgo WITH IDENTITY = 'Mary5',
SECRET = '<EnterStrongPasswordHere>';
GO
The following example uses a previously created account called User1OnEKM
on an EKM module through the EKM's Management tools, with a basic account type and password. The sysadmin account on the server creates a credential that is used to connect to the EKM account, and assigns it to the User1
SQL Server account:
CREATE CREDENTIAL CredentialForEKM
WITH IDENTITY='User1OnEKM', SECRET='<EnterStrongPasswordHere>'
FOR CRYPTOGRAPHIC PROVIDER MyEKMProvider;
GO
/* Modify the login to assign the cryptographic provider credential */
ALTER LOGIN User1
ADD CREDENTIAL CredentialForEKM;
The following example creates a SQL Server credential for the Database Engine to use when accessing the Azure Key Vault using the SQL Server Connector for Microsoft Azure Key Vault. For a complete example of using the SQL Server Connector, see Extensible Key Management Using Azure Key Vault (SQL Server).
Важно
The IDENTITY argument of CREATE CREDENTIAL requires the key vault name. The SECRET argument of CREATE CREDENTIAL requires the <Client ID> (without hyphens) and <Secret> to be passed together without a space between them.
In the following example, the Client ID (11111111-2222-3333-4444-555555555555
) is stripped of the hyphens and entered as the string 11111111222233334444555555555555
and the Secret is represented by the string SECRET_DBEngine
.
USE master;
CREATE CREDENTIAL Azure_EKM_TDE_cred
WITH IDENTITY = 'ContosoKeyVault',
SECRET = '11111111222233334444555555555555SECRET_DBEngine'
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov ;
The following example creates the same credential by using variables for the Client ID and Secret strings, which are then concatenated together to form the SECRET argument. The REPLACE function is used to remove the hyphens from the Client ID.
DECLARE @AuthClientId uniqueidentifier = '11111111-AAAA-BBBB-2222-CCCCCCCCCCCC';
DECLARE @AuthClientSecret varchar(200) = 'SECRET_DBEngine';
DECLARE @pwd varchar(max) = REPLACE(CONVERT(varchar(36), @AuthClientId) , '-', '') + @AuthClientSecret;
EXEC ('CREATE CREDENTIAL Azure_EKM_TDE_cred
WITH IDENTITY = ''ContosoKeyVault'', SECRET = ''' + @PWD + '''
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov ;');
Applies to: SQL Server 2014 (12.x) through current version and Azure SQL Managed Instance.
The following example creates a shared access signature credential using a SAS token. For a tutorial on creating a stored access policy and a shared access signature on an Azure container, and then creating a credential using the shared access signature, see Tutorial: Use Microsoft Azure Blob Storage with SQL Server databases.
Важно
THE CREDENTIAL NAME argument requires that the name match the container path, start with https and not contain a trailing forward slash. The IDENTITY argument requires the name, SHARED ACCESS SIGNATURE. The SECRET argument requires the shared access signature token.
The SHARED ACCESS SIGNATURE secret should not have the leading ?.
USE master
CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>] -- this name must match the container path, start with https and must not contain a trailing forward slash.
WITH IDENTITY='SHARED ACCESS SIGNATURE' -- this is a mandatory string and do not change it.
, SECRET = 'sharedaccesssignature' -- this is the shared access signature token
GO
The following example creates the credential that represent Managed Identity of Azure SQL or Azure Synapse service. Password and secret are not applicable in this case.
CREATE CREDENTIAL ServiceIdentity WITH IDENTITY = 'Managed Identity';
GO
Applies to: SQL Server 2022 (16.x) and later versions
The open S3-compatible standard provides for storage paths and details that may differ based on the storage platform. For more information, see SQL Server backup to URL for S3-compatible object storage.
For most S3-compatible storage, this example creates a server level credential and performs a BACKUP TO URL
.
USE [master];
CREATE CREDENTIAL [s3://<endpoint>:<port>/<bucket>]
WITH
IDENTITY = 'S3 Access Key',
SECRET = '<AccessKeyID>:<SecretKeyID>';
GO
BACKUP DATABASE [SQLTestDB]
TO URL = 's3://<endpoint>:<port>/<bucket>/SQLTestDB.bak'
WITH FORMAT /* overwrite any existing backup sets */
, STATS = 10
, COMPRESSION;
However, AWS S3 supports two different standards of URL.
S3://<BUCKET_NAME>.S3.<REGION>.AMAZONAWS.COM/<FOLDER>
(default)S3://S3.<REGION>.AMAZONAWS.COM/<BUCKET_NAME>/<FOLDER>
There are multiple approaches to successfully creating a credential for AWS S3:
Provide the bucket name and path and region in the credential name.
-- S3 bucket name: datavirtualizationsample
-- S3 bucket region: us-west-2
-- S3 bucket folder: backup
CREATE CREDENTIAL [s3://datavirtualizationsample.s3.us-west-2.amazonaws.com/backup]
WITH
IDENTITY = 'S3 Access Key'
, SECRET = 'accesskey:secretkey';
GO
BACKUP DATABASE [AdventureWorks2022]
TO URL = 's3://datavirtualizationsample.s3.us-west-2.amazonaws.com/backup/AdventureWorks2022.bak'
WITH COMPRESSION, FORMAT, MAXTRANSFERSIZE = 20971520;
GO
Or,
CREATE CREDENTIAL [s3://s3.us-west-2.amazonaws.com/datavirtualizationsample/backup]
WITH
IDENTITY = 'S3 Access Key'
, SECRET = 'accesskey:secretkey';
GO
BACKUP DATABASE [AdventureWorks2022]
TO URL = 's3://s3.us-west-2.amazonaws.com/datavirtualizationsample/backup/AdventureWorks2022.bak'
WITH COMPRESSION, FORMAT, MAXTRANSFERSIZE = 20971520;
GO
Or, provide the bucket name and path in the credential name, but parameterize the region within each BACKUP
/RESTORE
command. Use the S3-specific region string in the BACKUP_OPTIONS
and RESTORE_OPTIONS
, for example, '{"s3": {"region":"us-west-2"}}'
.
-- S3 bucket name: datavirtualizationsample
-- S3 bucket region: us-west-2
-- S3 bucket folder: backup
CREATE CREDENTIAL [s3://datavirtualizationsample.s3.amazonaws.com/backup]
WITH
IDENTITY = 'S3 Access Key'
, SECRET = 'accesskey:secretkey';
GO
BACKUP DATABASE [AdventureWorks2022]
TO URL = 's3://datavirtualizationsample.s3.amazonaws.com/backup/AdventureWorks2022.bak'
WITH
BACKUP_OPTIONS = '{"s3": {"region":"us-west-2"}}' -- REGION AS PARAMETER)
, COMPRESSION, FORMAT, MAXTRANSFERSIZE = 20971520;
GO
RESTORE DATABASE AdventureWorks2022_1
FROM URL = 's3://datavirtualizationsample.s3.amazonaws.com/backup/AdventureWorks2022.bak'
WITH
MOVE 'AdventureWorks2022'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2022_1.mdf'
, MOVE 'AdventureWorks2022_log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2022_1.ldf'
, STATS = 10, RECOVERY
, REPLACE, RESTORE_OPTIONS = '{"s3": {"region":"us-west-2"}}'; -- REGION AS PARAMETER)
GO
Събитие
Получете сертификат за Microsoft Fabric – безплатно!
19.11, 23 ч. - 10.12, 23 ч.
За ограничен период от време екипът на общността на Microsoft Fabric предлага безплатни ваучери за изпит DP-600.
Подгответе се сега