Post reply

Credentials

  • December 11, 2009 at 7:48 am

    #207348

    I created a credential using t-sql for my asl agent jobs but how would i script out the credential ?

    Also can i give existing sql server service account for credential ?

  • December 15, 2009 at 2:17 pm

    #1092735

    I would like to see what is in credential.

    Also is it mandatory that if i associate a credential with domain user i need to give same pswd as domain user pswd ?

  • December 16, 2009 at 11:24 am

    #1093222

    The script for creating a credential is simple:

    CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'DOMAIN\FakeUserName', SECRET = 'awfulpassword';

    You can generate a script using Management Studio by creating a new credential, entering the values (don't click OK yet!) and clicking the "Script" button at the top of the New Credential dialog.

    Be aware that the password for the credential DOES need to match that of the domain user's password, and that when scripted, it is plain text, not encrypted.

    In my environment, we run the SQL Server and Agent services as least-privileged users, and we occasionally need to access certain resources that the SQL Server shouldn't normally have access to (such as files outside of the SQL Server installation, or network shares) using SQL Agent jobs. We use credentials and SQL Agent proxies to allow us to do this.

    You can also map credentials to logins on the SQL Server, but I don't really know how that works because we don't have a need to use that feature.

    To set up the proxy for use by SQL Agent, there are stored procedures that need to be run to associate a proxy with a credential, and then allow that proxy to be used for particular types of SQL Agent job steps.

    -- Create the Proxy

    EXEC msdb.dbo.sp_add_proxy @proxy_name = 'MyProxy', @credential_name = 'MyCredential', @enabled = 1

    -- Allow proxy to be used for job steps

    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy', @subsystem_name = 'ActiveScripting'

    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy', @subsystem_name = 'CmdExec'

    ...

    You can then select the proxy in the "Run As" list on the job step types you have allowed it to be used for.

    As far as creating a credential using the SQL Server service account, you can probably do it, but I don't think it would be necessary, because access to outside resources from SQL Server without a credential specified will be done with the context of the SQL Server service account. I suppose it may be one way to solve an issue with a SQL Agent job step where the SQL Server service account has access to something the SQL Agent service account does not, but it may also indicate that the privileges allowed to the SQL Server service account need to be reviewed.

    Hope this helps.

    -- J.Kozloski, MCDBA, MCITP

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply