December 4, 2014 at 7:31 am
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.
December 4, 2014 at 7:47 am
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
December 10, 2014 at 10:09 am
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
desireclue that lack of resources.
ftfy 😀
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
December 10, 2014 at 10:47 am
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
December 10, 2014 at 12:12 pm
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 MASTERgo
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.
December 10, 2014 at 3:08 pm
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
Viewing 6 posts - 61 through 65 (of 65 total)
You must be logged in to reply to this topic. Login to reply