none
what is the difference between Service account,windows authentication,sql server authentication ,Sql agent and proxy account

    Question

  • Hi All

    I am a ASP.NET developer and as far as i know there are two types of account when we install sql server

    1)Windows authentication

    2)Mixmode (Windows authentication + Sql authetication)

    Now am going to work with SSIS Packages and in my team people are using different terms like

    • Service account
    • Sql agent accuont
    • proxy account

    to run the SSIS package what actually needed...

    We are actually working on migration project that is Package develped in sql 2005 bids + Windows 2003 server to

    Sql 2008r2 bids +windows 2008r2

    Please help me what is the use of proxy account and others 


    ilikemicrosoft

    Tuesday, July 23, 2013 4:35 PM

Answers

  • A SQL Server Job that is scheduled to run an SSIS package is executed by SQL Server Agent.  That's a Windows Service, and it's usually set up to use a Windows service account.  You can choose a different Windows service account, or any Windows account, when you set up the Job.  These are all Windows accounts;  SQL Server accounts are internal to SQL Server.

    You may encounter the term "proxy account" when using xp_cmdshell, which can be set up with sp_xp_cmdshell_proxy_account if you want someone other than sysadmin to use it (from within SQL Server).  This is probably no longer the best way to execute an SSIS package, though.

    Tuesday, July 23, 2013 4:45 PM
  • Pull up Control Panel -> Administrative Tools -> Services.  Find "SQL Server Agent" in the list of services.  Pull up its Properties window.  Select the "Log On" tab.  Look at the Windows account that is selected.

    This is how the SQL Server Agent process runs.  It runs under a Windows login account.  It could be the Local System account.  It could be NETWORK SERVICE.  It could be LOCAL SERVICE.  Whatever is selected here.

    You could type in your own user account and password, if your privileges are sufficient.  Then everything that SQL Server Agent does will be done acting as "you".  This is usually not a good idea, and people will often create a dedicated Windows service account for these types of services.  Then, when something gets messed up, it may be easier to track down which service broke it.

    These are all Windows operating system processes.  That's separate from the question of how SQL Server (which is, itself, running as a service - look it up in the list!) authenticates users so they can touch a database.  One way that SQL Server can do this authentication is by looking at the Windows login account of the interative user (or the batch process) that is trying to touch a database.  But it can also use its own "SQL Server authentication" - for example, the "sa" account, which is NOT a Windows login - so that's what gives you "mixed mode" authentication.

    Pull up SQL Server Management Studio, and select Security -> Logins.  Look at the list of users.  If you see something like "DOMAIN\username" you are probably looking at a Windows account.  If it's just "username" (e.g., "sa"), then you are probably looking at a SQL Server login.

    (Either type of login can be given a user mapping to one or more databases.  That's when they will show up under the other Security tab, within the individual databases.)

    Wednesday, July 24, 2013 12:48 PM

All replies

  • Hello,

    A service account is a Windows account which is used for a Windows service to logon to the system. A "SQL Agent account" is the service account of the Windows service "SQL-Server Agent".

    A proxy account is a different Windows account, which can be used to execute a SQL Server-Agent job with that credentials, e.g. if the service account don't have enough permissions to execute the job directly.

    Windows and SQL Login are the different authentication modes to logon to SQL Server.


    Olaf Helper

    Blog Xing

    Tuesday, July 23, 2013 4:42 PM
  • A SQL Server Job that is scheduled to run an SSIS package is executed by SQL Server Agent.  That's a Windows Service, and it's usually set up to use a Windows service account.  You can choose a different Windows service account, or any Windows account, when you set up the Job.  These are all Windows accounts;  SQL Server accounts are internal to SQL Server.

    You may encounter the term "proxy account" when using xp_cmdshell, which can be set up with sp_xp_cmdshell_proxy_account if you want someone other than sysadmin to use it (from within SQL Server).  This is probably no longer the best way to execute an SSIS package, though.

    Tuesday, July 23, 2013 4:45 PM
  • Hi,

    Thanks for your immediate response..

    i dont understand the below

    "A service account is a Windows account which is used for a Windows service to logon to the system"

    Could you please help me in understanding the above with some examples.


    ilikemicrosoft

    Wednesday, July 24, 2013 4:32 AM
  • Pull up Control Panel -> Administrative Tools -> Services.  Find "SQL Server Agent" in the list of services.  Pull up its Properties window.  Select the "Log On" tab.  Look at the Windows account that is selected.

    This is how the SQL Server Agent process runs.  It runs under a Windows login account.  It could be the Local System account.  It could be NETWORK SERVICE.  It could be LOCAL SERVICE.  Whatever is selected here.

    You could type in your own user account and password, if your privileges are sufficient.  Then everything that SQL Server Agent does will be done acting as "you".  This is usually not a good idea, and people will often create a dedicated Windows service account for these types of services.  Then, when something gets messed up, it may be easier to track down which service broke it.

    These are all Windows operating system processes.  That's separate from the question of how SQL Server (which is, itself, running as a service - look it up in the list!) authenticates users so they can touch a database.  One way that SQL Server can do this authentication is by looking at the Windows login account of the interative user (or the batch process) that is trying to touch a database.  But it can also use its own "SQL Server authentication" - for example, the "sa" account, which is NOT a Windows login - so that's what gives you "mixed mode" authentication.

    Pull up SQL Server Management Studio, and select Security -> Logins.  Look at the list of users.  If you see something like "DOMAIN\username" you are probably looking at a Windows account.  If it's just "username" (e.g., "sa"), then you are probably looking at a SQL Server login.

    (Either type of login can be given a user mapping to one or more databases.  That's when they will show up under the other Security tab, within the individual databases.)

    Wednesday, July 24, 2013 12:48 PM