SQL DBA knowing user passwords

Question

SQL DBA knowing user passwords

Viewing 6 posts - 61 through 65 (of 65 total)

You must be logged in to reply to this topic. Login to reply

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 1

Brandie Tarvin (12/4/2014)
Eric M Russell (12/4/2014)
I get the impression this is not commonly known, but a SQL Server authenticated user account, even a minimal user with nothing but default public permissions, can change it's own password at any point after it's created by the DBA.
But that requires access to tools that allow one to write / parse T-SQL, correct? For instance, if someone doesn't have SSMS or Powershell or the SQL command line tool, the user can't make those password changes.
Yes?

That is the problem. Most SQL Server based small applications have you start the application up and log in with a SQL Server authenticated userid with no way to sense and change the password. Trying to get Mgt Studio installed on hundreds of PCs and then trying to explain to a Non IT person how to open up Mgt Studio, log in and save this DDL somewhere and what to do is not going to work.

Oracle is the same way, this isn't a SQL Server issue entirely. With some Oracle Apps we just have the password expire then the users have to call the DBA staff to reset it. Not a good thing at all. The things we have in SQL Server that use SQL Auth are not critical data things so it isn't that big of a deal.

Eric M Russell SSC Guru Points: 125562 More actions · Answer 2

Brandie Tarvin (12/4/2014)
Eric M Russell (12/4/2014)
I get the impression this is not commonly known, but a SQL Server authenticated user account, even a minimal user with nothing but default public permissions, can change it's own password at any point after it's created by the DBA.
But that requires access to tools that allow one to write / parse T-SQL, correct? For instance, if someone doesn't have SSMS or Powershell or the SQL command line tool, the user can't make those password changes.
Yes?

Excel or MS Access can do the job, and there are also some zero install apps like FlySpeed SQL that can be downloaded from the web or brought in on thumb drive. Basically any app that can connect to a SQL Server instance and issue SQL commands will do.

My point is that even if the DBA insists on knowing the password for accounts owned by developers or ad-hoc query users, if a user knows the password, then they can always change it at any time without going through the DBA.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

andrew gothard SSChampion Points: 12315 More actions · Answer 3

lshanahan (11/26/2014)
Markus (11/25/2014)
You guys must live in a perfect world of applications... However, the bulk of the SQL Server based apps are small companies and don't have the resources or the desire to use Network authentication or use passsword sync toolsets.
Does this scare anyone else besides me? I'm far from the best programmer in the world but using Windows auth is not that difficult. Smacks more of lack of ~~desire~~ clue that lack of resources.

ftfy 😀

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

Eric M Russell SSC Guru Points: 125562 More actions · Answer 4

lshanahan (11/26/2014)
Markus (11/25/2014)
You guys must live in a perfect world of applications... However, the bulk of the SQL Server based apps are small companies and don't have the resources or the desire to use Network authentication or use passsword sync toolsets.
Does this scare anyone else besides me? I'm far from the best programmer in the world but using Windows auth is not that difficult. Smacks more of lack of desire that lack of resources.
But the guiding principle that's been hammered into my head is users at all levels only need minimum access and rights to do their job and no more.

I see no reason why a 3rd party application, whether it be a CRM application or a performance monitoring tool, can't use Windows authentication. It does require that the network admin create the account and DBA grant appropriate permissions, but the application shouldn't care; it's just a matter of what's in the connection string.

As for those applications (or power users) that insist on using the 'SA' account; well they can login as 'SA' if they want, but the DBA can still dictate what permissions this account has by dropping and recreating it like so:

use MASTER

go

alter login [sa] DISABLE;

go

alter login [sa] with name = [sa_bak];

go

create login [sa] with PASSWORD = 'wh@t3v3r1210', DEFAULT_DATABASE = master;

go

use Accounting;

go

drop user [sa];

go

create USER [sa] for login [sa];

go

exec sp_addrolemember 'db_datareader', 'sa';

exec sp_addrolemember 'db_datawriter', 'sa';

go

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 5

Eric M Russell (12/10/2014)
lshanahan (11/26/2014)
Markus (11/25/2014)
You guys must live in a perfect world of applications... However, the bulk of the SQL Server based apps are small companies and don't have the resources or the desire to use Network authentication or use passsword sync toolsets.
Does this scare anyone else besides me? I'm far from the best programmer in the world but using Windows auth is not that difficult. Smacks more of lack of desire that lack of resources.
But the guiding principle that's been hammered into my head is users at all levels only need minimum access and rights to do their job and no more.
I see no reason why a 3rd party application, whether it be a CRM application or a performance monitoring tool, can't use Windows authentication. It does require that the network admin create the account and DBA grant appropriate permissions, but the application shouldn't care; it's just a matter of what's in the connection string.
As for those applications (or power users) that insist on using the 'SA' account; well they can login as 'SA' if they want, but the DBA can still dictate what permissions this account has by dropping and recreating it like so:
use MASTER
go
alter login [sa] DISABLE;
go
alter login [sa] with name = [sa_bak];
go
create login [sa] with PASSWORD = 'wh@t3v3r1210', DEFAULT_DATABASE = master;
go
use Accounting;
go
drop user [sa];
go
create USER [sa] for login [sa];
go
exec sp_addrolemember 'db_datareader', 'sa';
exec sp_addrolemember 'db_datawriter', 'sa';
go

Wow. I did not know the SA login could be altered. I'm going to have to try that little trick. Thanks for the code.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Eric M Russell SSC Guru Points: 125562 More actions · Answer 6

Brandie Tarvin (12/10/2014)
...
Wow. I did not know the SA login could be altered. I'm going to have to try that little trick. Thanks for the code.

Yeah, it's kind of funny to get an email alert informing that some dork failed to alter a stored procedure or change the MAXDOP setting while logged in as 'SA' account. The real DBA doesn't login as 'SA', although there is a SQL Server account with sysadmin privillage which shall remain un-named. It's becomes a honeypot to trap developers who try to run a late Friday afternoon "black op" deployment. 😎

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho