RE: SQL Authentication



OK Brad, I can see a couple ways you can do this.

First of all though, I want to tell you that the whole point of having
separate instances is to separate security, so there's not way to
automatically cascade the user accts or keep them in synch natively like
that. That being said here are your alternatives:

1. You can use the new Katmai SSMS to put all the instances into a group
and teach your users to change their passwords with tsql instead of the gui.
This way you're keeping them in synch, but your doing it through group
administration in SSMS. It's a good use of that technology.

2. You can write a separate app or a web page for them to change their
passwords. When they enter their username/password it'll cycle through all
the instances on the backend and change them. This is really the same idea
as the first one, just done in .net instead of the gui.

But you really don't have too many other options. They're all going to be
something like that.
--
Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground -- http://weblog.infoworld.com/dbunderground/
DBA Rant – http://dbarant.blogspot.com




"Brad Baker" wrote:

Sorry for the cross posting. I also posted this on
microsoft.public.sqlserver.server by accident!
---

The company I work for has 4 Microsoft SQL clusters (running SQL 2000 & SQL
2005) with 3 SQL instances per cluster (so 12 instances total). Historically
we've used the "sa" account for pretty much all access to the databases. We
recognize that this is a bad security practice and would like to correct the
situation but we're having some challenges in doing so.

Primarily we do not believe we can use windows based authentication
exclusively. Our SQL server and employee PCs are not part of the same active
directory domain and without getting into all the specifics, we cannot
change this. It appears with DSN based SQL connections it is not possible to
specify windows credentials, as best as we can tell, ODBC utilizes the
credentials of the current logged in user.

Under the circumstances we believe the only remaining option is SQL
authentication. The problem is that we have 30 or so users which need access
to 300+ databases spread across these various SQL clusters. We know we can
create individual SQL accounts on every SQL instance/server but then how do
we manage usernames and passwords across the SQL instances?

When a user wants to change their SQL password we don't want them to have to
sign into 12+ SQL instances and manually update their password. We'd like
them to have a single SQL username and password which grants them access to
all the SQL instances. Or if we absolutely must have separate accounts, when
they change the account on one server it should synchronize to the other
servers.

I'm not sure if this is possible and if so how it would be done. I've done
some cursory searches on Google but I'm not having especially good luck. I
was hoping someone might be able to point me in the right direction or offer
some suggestions.

Thanks,
Brad


.



Relevant Pages

  • Password Compatibility?
    ... a security configuration for our domain, ... password for the account that I am trying to use. ... Does sql 2005, using the SQL Server Configuration Manager, support passwords ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL Server Memory hog
    ... Symantec to see how much RAM they recommend allocating to their database. ... looked at my SQL instances and I have many that are using less than 100 MB. ... It was set to Dynamic Memory Configuration using a ...
    (microsoft.public.windows.server.sbs)
  • SQL Authentication
    ... we've used the "sa" account for pretty much all access to the databases. ... Our SQL server and employee PCs are not part of the same active ... sign into 12+ SQL instances and manually update their password. ...
    (microsoft.public.sqlserver.security)
  • Re: WSRM CPU Allocation Policy - Multiple SQL Instances
    ... Create 6 Process Matching Criteria - In GUI select the running ... services and choose the appropriate SQL service. ... > I'm currently wanted to use WSRM on W2k3 Enterprise Edition that is ... > I've got 6 different SQL instances on the same box, ...
    (microsoft.public.windows.server.general)
  • Re: Is SQLDMO the preferred way to detect SQL2008?
    ... you can use any means to first enumerate all the installed ... I used to search our entire domain for installed SQL instances. ... the script will enumerate the installed services and check if their names ...
    (microsoft.public.sqlserver.setup)