 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Other SQL Server Topics / April 2007User authentication
Hi experts! When using SQL Server Management Studio to connecto SQL Server 2005, we are given options to connect using Windows authentication or SQL authentication. My question is, when using Windows authentication, how do we choose to use other Windows accounts? The login section becomes grey and it automatically uses Windows account that a user is currently logged on as. What I want to do is setup a maintenance plan and do the work from my workstation. When setting up a backup task, it also gives options for me to use Windows or SQL authentication. Again when I choose Windows authentication, it does not allow me to pick other accounts. What I already have is a generic backup account to be used for this purpose. Could anyone please enlighten me? Thank you in advance. William A. J. When you chose to use Windows Authentication, generally you will just be using the windows user account that the person used to log into the PC or server from where they are connecting to SQL Server. So if I log into the domain (onto my PC) at work using SomeWork\Someone then that's the login that will be passed to SQL Server for authentication. If I log out and log in again using SomeWork\Another, then SomeWork\Another is what will be used. The options are grayed out as the credentials you used when initially logging onto your PC are the credentials that will be used. In terms of the backup task, it's not real clear where you are viewing the authentication, logins to select. I would guess it could be you have a maintenance plan and you are looking at the connections. If you used the wizard, the selection for authentication is the same thing. It's the authentication used when they package connects to the database. I'm not clear on what you are trying to do with a maintenance plan and "do the work from my workstation". Are you trying to schedule this? What do you mean by do the work? Generally, you'd set up the whatever jobs or maintenance plans to run on the server itself and schedule these with SQL Agent jobs. The security context for these is first determined by the Agent setting, proxies if you are using those, job owner, etc. The following articles cover Agent security and proxies: http://msdn2.microsoft.com/en-us/library/ms190926.aspx http://msdn2.microsoft.com/en-us/library/ms189064.aspx -Sue >Hi experts! > [quoted text clipped - 15 lines] > >William A. J. Hi Sue, Thanks for the response. What I want to do is configure scheduled backup. However, if possible, I would like to use Windows authentication as opposed to SQL authentication. What I have in mind is that it would be easier from administration point of view this way rather than having separate user management, Windows and Database. This is where I am not sure about. If I log on from a workstation and create a backup job with Windows authentication, it is going to use my account to run the job. Am I correct? What happen if my account has been removed? Will the backup still run? If I have a backup operator account and I want to use it to run the backup job, I have to log on to my workstation as the backup operator, then create the backup job in SQL server. Is there a workaround on this? It is a bit of a hassle having to log out from the workstation and login as the backup operator to create a backup job. Thank you in advance. > When you chose to use Windows Authentication, generally you > will just be using the windows user account that the person [quoted text clipped - 47 lines] > > > >William A. J. Okay...so if all you want to do is schedule a backup and then have it run under windows authentication and reduce any time managing account, you may be making it harder than it is. The owner of the job is a significant factor. If the job is owned by a sysadmin, the job executes under the security context of the SQL Server Agent service account. If the job is owned by a non-sysadmin, it will execute under the security context of the proxy account. For backups, it's typical and much easier to manage if you just set the job to be owned by a sysadmin. The job will run under the security context of the SQL Agent service. You can create the job under whatever security context you want to log in as - as long as you are a sysadmin, you can set the job to be owned by a sysadmin account. You can have the owner be the service account or sa. -Sue >Hi Sue, > [quoted text clipped - 68 lines] >> > >> >William A. J. Hi Sue, The Windows service account dedicated to run backup inherits the access of sysadmin, so it is as powerful. So I kind of have 2 options here. If I use "sa", I have to maintain separate account management. If I use Windows backup operator account, I have to change login all the time on my workstation to make changes to backup jobs. So my question is, is there a workaround on the 2nd option? I really want to use Windows backup operator account without having to change login everytime I access the backup jobs. Thank you. William A. J. > Okay...so if all you want to do is schedule a backup and then have it > run under windows authentication and reduce any time managing account, [quoted text clipped - 84 lines] > >> > > >> >William A. J. I'm not sure you are really following how it works. The job will run under the security context of either the SQL Server Agent Service account or the Proxy Account. Which account is used depends on who owns the job. There is no need to log in and out using one of those accounts. You can manage jobs with your login even if they are going to run under another security context. Or be owned by a login other than yours. There is no separate account management. Why don't you just let the service account backup the databases? That is how it's generally done If you really have to use another account (this is actually increasing your maintenance and account management) and use your windows backup operator account then you need to setup that account to be the proxy account, give it the necessary access and permissions and then have the job owned by a non-sysadmin.. -Sue >Hi Sue, > [quoted text clipped - 98 lines] >> >> > >> >> >William A. J. Hi Sue, What the problem really is for me at the moment is because the infrastructure of the company is a little messy. There are 2 SQL Server 2005 installed on different servers, 1 SQL Server 2000 installed on another server and a few MSDE installed on the rest. That means if I have to rely on SQL accounts, there are many of them. I do not even have logon details of some of the "sa" accounts due to them installed by dodgy outsourcing companies. Those details were never given to me. What I mean by separate account management is having to use SQL accounts to run backup jobs. So I am trying to do is use Windows account instead to run backup jobs. What I have found is when I am logged on as myself, I can configure jobs to be owned by other accounts only if those accounts are SQL accounts. I cannot configure jobs to be owned by other Windows accounts. Thank you. William A. J. > I'm not sure you are really following how it works. The job > will run under the security context of either the SQL Server [quoted text clipped - 119 lines] > >> >> > > >> >> >William A. J. The windows accounts need to be added as logins in SQL Server prior to having them own jobs. -Sue >Hi Sue, > [quoted text clipped - 138 lines] >> >> >> > >> >> >> >William A. J. Hi Sue, Does security access of an account in a domain have any effect on its access on SQL? For example, an account is a member of Domain Admins. The account is added on SQL server and has a fairly limited access on it. What will the outcome be? Thank you in advance. > The windows accounts need to be added as logins in SQL > Server prior to having them own jobs. [quoted text clipped - 143 lines] > >> >> >> > > >> >> >> >William A. J. It can. Permissions are cumulative for all types of access. With Windows authentication, I can have access individually through MyDomain\SomeLogin or as a member of a windows group. And this would be all groups granted access. So I could have permissions based on permissions granted directly to me as well as permissions granted to all the Windows groups of which I am a member. Those types of things can make security easy to manage if things are thought out and planned for - where you manage access and permissions through membership in Windows groups. By default, there is a windows group named BUILTIN\Administrators that is a member of sysadmin server role. The members of that windows group are all local admins on the server. The default on the windows end of the security would be to have the Domain Admins group as members of the local admins group. Following this chain, domain admins would be in a windows group where they end up inheriting sysadmin rights. -Sue >Hi Sue, > [quoted text clipped - 152 lines] >> >> >> >> > >> >> >> >> >William A. J. Hi Sue, Thank you for the information. I however have a few more doubts if you would not mind. I have posted it but I will just quote it below so it is more convenient for you. --- There are a few things I need to clarify. - I was able to change the owner of a backup to the backup operator (Windows account) on a backup job configured on SQL Agent, eventhough the account had not been added to an MSDE. Is this normal? - If I set a backup job on SQL Server Maintenance Plan on SQL Server 2005, when creating a connection to a local or remote database (MSDEs in my situation), I am not able to assign the job to a different Windows account. I can only choose to use current Windows account I am logged on as or any SQL accounts. Would you be able to shed some light? --- Thank you so much. William A. J. > It can. Permissions are cumulative for all types of access. > With Windows authentication, I can have access individually [quoted text clipped - 174 lines] > >> >> >> >> > > >> >> >> >> >William A. J. Hi Sue, Thank you for the information. I however have a few more doubts if you would not mind. I have posted it but I will just quote it below so it is more convenient for you. --- There are a few things I need to clarify. - I was able to change the owner of a backup to the backup operator (Windows account) on a backup job configured on SQL Agent, eventhough the account had not been added to an MSDE. Is this normal? - If I set a backup job on SQL Server Maintenance Plan on SQL Server 2005, when creating a connection to a local or remote database (MSDEs in my situation), I am not able to assign the job to a different Windows account. I can only choose to use current Windows account I am logged on as or any SQL accounts. Would you be able to shed some light? --- Thank you so much. William A. J. > It can. Permissions are cumulative for all types of access. > With Windows authentication, I can have access individually [quoted text clipped - 174 lines] > >> >> >> >> > > >> >> >> >> >William A. J. Comments inline..... >Hi Sue, > [quoted text clipped - 9 lines] >account) on a backup job configured on SQL Agent, eventhough the account had >not been added to an MSDE. Is this normal? It's most likely getting access through group membership in one of the windows groups that has access to SQL Server. >- If I set a backup job on SQL Server Maintenance Plan on SQL Server 2005, >when creating a connection to a local or remote database (MSDEs in my [quoted text clipped - 3 lines] > >Would you be able to shed some light? You need to be logged in as a sysadmin to change the job owner to other logins. -Sue Hi Sue, This does the trick. I can change owner of the job to a backup operator account. It however only works on jobs setup on SQL Agent. There are a few things I need to clarify. - I was able to change the owner of a backup to the backup operator (Windows account) on a backup job configured on SQL Agent, eventhough the account had not been added to an MSDE. Is this normal? - If I set a backup job on SQL Server Maintenance Plan on SQL Server 2005, when creating a connection to a local or remote database (MSDEs in my situation), I am not able to assign the job to a different Windows account. I can only choose to use current Windows account I am logged on as or any SQL accounts. Would you be able to shed some light? Thank you in advance. William A. J. > The windows accounts need to be added as logins in SQL > Server prior to having them own jobs. [quoted text clipped - 143 lines] > >> >> >> > > >> >> >> >William A. J. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.