Re: User authentication

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.

"Sue Hoegemeier" wrote:

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

On Wed, 25 Apr 2007 17:52:00 -0700, William A. J.
<WilliamAJ@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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.

"Sue Hoegemeier" wrote:

The windows accounts need to be added as logins in SQL
Server prior to having them own jobs.

-Sue

On Tue, 17 Apr 2007 21:16:02 -0700, William A. J.
<WilliamAJ@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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.

"Sue Hoegemeier" wrote:

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

On Tue, 17 Apr 2007 18:58:00 -0700, William A. J.
<WilliamAJ@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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.

"Sue Hoegemeier" wrote:

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

On Mon, 16 Apr 2007 22:56:02 -0700, William A. J.
<WilliamAJ@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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.

"Sue Hoegemeier" wrote:

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

On Wed, 4 Apr 2007 19:28:44 -0700, William A. J.
<WilliamAJ@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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.










.

Relevant Pages

  • Re: User authentication
    ... I have to maintain separate account management. ... If I use Windows backup ... However, if possible, I would like to use Windows authentication as opposed ...
    (microsoft.public.sqlserver.clients)
  • Re: User authentication
    ... With Windows authentication, ... an account is a member of Domain Admins. ... Windows account instead to run backup jobs. ...
    (microsoft.public.sqlserver.clients)
  • Re: User authentication
    ... Server Agent service account. ... What I want to do is configure scheduled backup. ... However, if possible, I would like to use Windows authentication as opposed ...
    (microsoft.public.sqlserver.clients)
  • Re: Connect problem MSDE ADO ASP
    ... problems on setting the IIS User password. ... result - and both can access the MSDE SQL via the webinterface (I am aware I ... > your connection string, and put in a valid SQL USER ACCOUNT, not a Windows ...
    (microsoft.public.sqlserver.programming)
  • Re: User authentication
    ... There are 2 SQL Server 2005 ... 1 SQL Server 2000 installed on another server ... Windows account instead to run backup jobs. ...
    (microsoft.public.sqlserver.clients)
Loading