locked
a question about CREDENTIAL RRS feed

  • Question

  •  

    If sqlserver perform a create/backup database operation, it would interact with OS.

    So it would use CREDENTIAL because the file(made by Create/Backup database operation) was a resource outside SQLSERVER. Right?   If so, what's windows user the CREDENTIAL bind with?

     

    any suggestions?

    Wednesday, September 3, 2008 2:10 PM

Answers

  • There a variety of different this that could be used.  After creating a credential you can then use that credential in a proxy. 

    1. From SQL Server Managament Studio open SQL Server Agent

    2. Open the Proxies folder

    Each one of these different tasks can use a proxy to execute them.  Consider if you are running an ActiveX script which is accessing a file/folder secured with NTFS permissions.  The permissions require an administrative account to access this file/folder only for this one step in the scheduled job.  Rather than assigning SQL Server Agent an administrative log on, you can:

    1. Create a Credential that is mapped to an administrative user,

    2. Create a Proxy for ActiveX tasks under the SQL Server Agent that uses the Credential

    3. Assign the Proxy to the single ActiveX step in the job.

    Rather than having to escalate the logon security context of SQL Server Agent to an administrator you only have one single step that will run in this escalated context.

     

    The above example is how I actually implemented such a Proxy.

     

    Let me know if this makes sense.  Also fo through the MSDN link I posted on creating Proxies.  It will make more sense if you are able to walk through it and see the usefulness in your own environment.

     

     

    Sunday, September 7, 2008 7:22 PM

All replies

  • I am not sure I really understand the question, but I believe you are refering to the security context that a database file/back up file is used or created.  If the file is created using sql server then the squ server service account's security context is what will need to have access to the specified folder/files.  If it is a backup then the sql server agent will need access.

     

    Please let me know if I misunderstood.

     

    Wednesday, September 3, 2008 3:40 PM
  • Thanks David

     

     David Dye wrote:

     If the file is created using sql server then the squ server service account's security context is what will need to have access to the specified folder/files.

     

     

    does it mean the sql login that create database would use the CREDENTIAL bind with the sqlserver service account?

    If yes,  what is the CREDENTIAL?  I can't find it in sys.credentials dmv

     

    Thursday, September 4, 2008 3:08 AM
  • Assuming you're talking about the objects created with CREATE CREDENTIAL, these can be used with xp_cmdshell or with SQL Agent jobs. Unless memory fails me, there is no other use for them at this point. Also, the CREDENTIAL option for logins is not used yet - that syntax is reserved for future features.

     

    Thanks

    Laurentiu

    Thursday, September 4, 2008 5:39 PM
  • Apologize for my poor english first. That make you confused what I want to ask.

     

     Now go back to my original question:

     

      What i want to know is : what's the CREDENTIAL sql server used if sqlserver itself interact(backup database) with OS ?

    Say I have a instance which the sqlserver service account is 'domain user' . I perform a 'backup database' operation to other machine which the domain user have permission to access, the 'backup database' opersation will perform under the security context of the 'domain user' .

    Right?  will sql server use a CREDENTIAL during this process ? If yes ,what is the CREDENTIAL, is that a system CREDENTAIL?

     

     

     thanks very much

    Saturday, September 6, 2008 4:16 AM
  • You are correct.  The security context used by the sql server service is what will be used to access the file and folder group.  If you are using a scheduled job to back up the database then the security context used by sql server agent will be used.

     

    Example:

    Backing up manually from SQL Server Management Studio, sql server service uses the domain\user1 domain account as a log on, the folder/file where the backup will be saved must allow domain\user1 access

     

    Backing up using a scheduled job with a single t-sql step, sql server agent uses the domain\user2 domain account as log on,  the folder/file where the backup will be saved must allow domain\user2 access

     

    Not to confuse things, but there is a credential in sql server also.  The link to sql server credentials is here:

    http://msdn.microsoft.com/en-us/library/ms190703.aspx

    Credentials in SQL Server are directly related to proxy's, the link for createing is listed below.  The proxy can be used for a single step in a sql server job to run under this security context.  This provides the ability to run the sinlge step in an escalated secuirty context instead of having to escalate the log on credentials of sql server agent.

    http://msdn.microsoft.com/en-us/library/ms190698.aspx

     

    Please let me know if this answers your question or if I misunderstood.

     

     

     

    Saturday, September 6, 2008 12:46 PM
  •  David Dye wrote:

    You are correct.  The security context used by the sql server service is what will be used to access the file and folder group.  If you are using a scheduled job to back up the database then the security context used by sql server agent will be used.

     

    Example:

    Backing up manually from SQL Server Management Studio, sql server service uses the domain\user1 domain account as a log on, the folder/file where the backup will be saved must allow domain\user1 access

     

    Backing up using a scheduled job with a single t-sql step, sql server agent uses the domain\user2 domain account as log on,  the folder/file where the backup will be saved must allow domain\user2 access

     

    Not to confuse things, but there is a credential in sql server also. 

     

    Please let me know if this answers your question or if I misunderstood.

     

    Hi David,

     

       Does that mean 'manual Backup' don't use credential?  Only  sql server agent would use credential?

    Saturday, September 6, 2008 3:42 PM
  • No.  When you manually back up a database the security context that is used is that of the logon for the sqlserver service.  So if the sql server service is started using DOMAIN\user1 then that is the security context that will be used.

     

    Saturday, September 6, 2008 3:46 PM
  • So SQLSERVER also use credential when it perform backup database manually?

    But where is CREDENTIAL? I can't find it in sys.credentials dmv. Is that a internal credential we can't see?

    Saturday, September 6, 2008 5:20 PM
  • Credentials are completely separate from the security context assigned to the sql server service account.  Are a completely separate object in sql server and when you create a backup manually from SQL Server Management Studio you are using ONLY the security context of the SQL Server Service account.

    Please this hyperlink which discusses credentials.

    http://msdn.microsoft.com/en-us/library/ms190703.aspx

     

    Go to SQL Server Management Studio:

    1. Open the Security folder

    2. Right click the Credential folder

    3. Select New Credential...

    4. Enter a name of your choice in the Name text box

    5. Enter in a domain or local user name or group in the Identity text box

    6. Enter the password for that user/group in the Password text box

    7. Confirm te password and click the Confirm Password textbox

    8. Click the OK button

     

    Now execute the below query:

    Code Snippet

    SELECT *

    FROM sys.credentials

     

     

    You should now get a single row reflecting the credential you just created.  This has nothing to do with the security context of the SQL Server Service, but can be used to create a proxy for a specific job step and type.  The below link discusses the creation of this:

    http://msdn.microsoft.com/en-us/library/ms190698.aspx

     

     

    Saturday, September 6, 2008 8:52 PM
  • Thanks David.

             I only know two objects would use CREDENTIAL:
    1.        xp_cmdshell
    2.        sql server agent

    Can you tell me other objects use CREDENTIAL?
    Sunday, September 7, 2008 4:30 PM
  • There a variety of different this that could be used.  After creating a credential you can then use that credential in a proxy. 

    1. From SQL Server Managament Studio open SQL Server Agent

    2. Open the Proxies folder

    Each one of these different tasks can use a proxy to execute them.  Consider if you are running an ActiveX script which is accessing a file/folder secured with NTFS permissions.  The permissions require an administrative account to access this file/folder only for this one step in the scheduled job.  Rather than assigning SQL Server Agent an administrative log on, you can:

    1. Create a Credential that is mapped to an administrative user,

    2. Create a Proxy for ActiveX tasks under the SQL Server Agent that uses the Credential

    3. Assign the Proxy to the single ActiveX step in the job.

    Rather than having to escalate the logon security context of SQL Server Agent to an administrator you only have one single step that will run in this escalated context.

     

    The above example is how I actually implemented such a Proxy.

     

    Let me know if this makes sense.  Also fo through the MSDN link I posted on creating Proxies.  It will make more sense if you are able to walk through it and see the usefulness in your own environment.

     

     

    Sunday, September 7, 2008 7:22 PM
  • Thanks  David.

     

       I attempt to create a job , use proxy in one step. it success.

     

       It seems like the 'alter/create login with credential   option is out of date, right?

     

    Monday, September 8, 2008 9:30 AM
  •  

    Great!  You can actually create a CREDENTIAL and then create a login referencing the CREDENTAIL.  This is an extra step rather than just creating the login directly from the Windows account, but here is the t-sql code to do this using a fictional Windows user called AlterEgo, this code is in Books on Line:

    Code Snippet

    CREATE CREDENTIAL AlterEgo

    WITH IDENTITY = 'RettigB',

        SECRET = 'sdrlk8$40-dksli87nNN8';

    GO

     

    CREATE LOGIN AlterEgo

    WITH PASSWORD = 'sdrlk8$40-dksli87nNN8',

        CREDENTIAL = AlterEgo;

    GO

     

     

    It's interesting to read Books on Line for SQL 2008:

     

    CREDENTIAL = credential_name

    The name of a credential to be mapped to the new SQL Server login. The credential must already exist in the server. Currently this option only links the credential to a login. This option might be expanded in a future SQL Server version.

    The bold and underlined text is new to Books on Line for SQL 2008 and was was not in the same section for SQL 2005.  Here is the link:

    http://msdn.microsoft.com/en-us/library/ms189751.aspx

     

    Hope this helps

     

    Monday, September 8, 2008 11:05 AM
  • Thanks againSmile

     

    Tuesday, September 9, 2008 2:34 AM