February 27, 2017 at 3:14 pm
Dev's have RO access through their domain account, but are using SQL login which has SA access to connect to SQL.
Anyway to identify the domain account who connects to SQL using SQL login?
February 27, 2017 at 3:21 pm
If they are using a SQL login, it will not map to a domain login.
What you could do is log the user, ip and hostname with a timestamp for every sa login that occurs. At least that will let you map the login back to a machine and then you can figure out the user who uses that machine.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 27, 2017 at 3:47 pm
manojgudapati - Monday, February 27, 2017 3:14 PMDev's have RO access through their domain account, but are using SQL login which has SA access to connect to SQL.
Anyway to identify the domain account who connects to SQL using SQL login?
Further on Jason's good reply, why on earth would devs have an sa access to the server, sounds bonkers to me!
😎
February 27, 2017 at 4:51 pm
Eirikur Eiriksson - Monday, February 27, 2017 3:47 PMmanojgudapati - Monday, February 27, 2017 3:14 PMDev's have RO access through their domain account, but are using SQL login which has SA access to connect to SQL.
Anyway to identify the domain account who connects to SQL using SQL login?Further on Jason's good reply, why on earth would devs have an sa access to the server, sounds bonkers to me!
😎
While I do share your view, it is really not that uncommon.
I've come across hard coded sa authentication in some in-house developed apps, so nothing surprises me anymore.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 27, 2017 at 5:05 pm
Henrico Bekker - Monday, February 27, 2017 4:51 PMEirikur Eiriksson - Monday, February 27, 2017 3:47 PMmanojgudapati - Monday, February 27, 2017 3:14 PMDev's have RO access through their domain account, but are using SQL login which has SA access to connect to SQL.
Anyway to identify the domain account who connects to SQL using SQL login?Further on Jason's good reply, why on earth would devs have an sa access to the server, sounds bonkers to me!
😎While I do share your view, it is really not that uncommon.
I've come across hard coded sa authentication in some in-house developed apps, so nothing surprises me anymore.
I know Henrico but it drives me nuts every time
😎
On a funny side note, your name is the same as mine, Henrico --> Enrico --> Eric --> Eirikur
February 27, 2017 at 5:32 pm
Henrico Bekker - Monday, February 27, 2017 4:51 PMEirikur Eiriksson - Monday, February 27, 2017 3:47 PMmanojgudapati - Monday, February 27, 2017 3:14 PMDev's have RO access through their domain account, but are using SQL login which has SA access to connect to SQL.
Anyway to identify the domain account who connects to SQL using SQL login?Further on Jason's good reply, why on earth would devs have an sa access to the server, sounds bonkers to me!
😎While I do share your view, it is really not that uncommon.
I've come across hard coded sa authentication in some in-house developed apps, so nothing surprises me anymore.
That is actually far too common. It is extremely frustrating. The best part is they use that account in plain text config files.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 2, 2017 at 6:23 am
SQLRNNR - Monday, February 27, 2017 5:32 PMHenrico Bekker - Monday, February 27, 2017 4:51 PMEirikur Eiriksson - Monday, February 27, 2017 3:47 PMmanojgudapati - Monday, February 27, 2017 3:14 PMDev's have RO access through their domain account, but are using SQL login which has SA access to connect to SQL.
Anyway to identify the domain account who connects to SQL using SQL login?Further on Jason's good reply, why on earth would devs have an sa access to the server, sounds bonkers to me!
😎While I do share your view, it is really not that uncommon.
I've come across hard coded sa authentication in some in-house developed apps, so nothing surprises me anymore.That is actually far too common. It is extremely frustrating. The best part is they use that account in plain text config files.
We do this at our company, for now. Being new to the MSSQL platform this will sound like a simple/silly question to most but what is the best practice for db access for in-house developed apps?
March 2, 2017 at 6:31 am
Custom account, minimal permissions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 2, 2017 at 6:45 am
Currently I create a SQL user sign-in account for each application and only give it permissions to the databases it needs (minimal permissions). I do know that the password is stored in plain text in the web.config file of the apps. Is this the best practice, or is there a better way?
UPDATE: Just realized what the "SA" account is. I thought it was a general reference to SQL sign-in accounts and not the "God" account of the SQL server. I didn't think anyone would be silly enough to give a SQL sign-in account SA level access so the thought didn't initally cross my mind.
March 2, 2017 at 10:24 am
Here is a an article that covers making the config file more secure.
https://msdn.microsoft.com/en-us/library/dtkwfdky.aspx
Sadly, people just use the SA account or some other application account that they added to the sysadmin role (equally as bad) for all of their application connections. Why? There are multiple reasons I can think of. The first is due to how easy it is to just give the account sysadmin permissions because everything will just work - no thought or effort involved.
The second reason is due to articles such as the following article that tell people to do it.
REALLY BAD SECURITY ADVICE
The advice in that preceding link is one of the things that prompted me to write the following article.
Why that last link is full of BAD!
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 7, 2017 at 12:41 pm
Given the critical level of importance related to permissions, one may think it is a concept that is well understood by all who are given the charge of protecting the data.
lol new to IT are we?
March 9, 2017 at 12:09 am
patrickmcginnis59 10839 - Tuesday, March 7, 2017 12:41 PMGiven the critical level of importance related to permissions, one may think it is a concept that is well understood by all who are given the charge of protecting the data.
lol new to IT are we?
Not seeing how you made that conclusion.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 9, 2017 at 6:27 am
SQLRNNR - Thursday, March 9, 2017 12:09 AMpatrickmcginnis59 10839 - Tuesday, March 7, 2017 12:41 PMGiven the critical level of importance related to permissions, one may think it is a concept that is well understood by all who are given the charge of protecting the data.
lol new to IT are we?
Not seeing how you made that conclusion.
The implication is that if you are brand new to IT, even having a great set of credentials, yet have never encountered those who might lack critical thinking skills and have only associated with those who neglect nothing on the way to best practices and theoretical purity, ie., you've never encountered the real world, then you "may think it is a concept that is well understood" etc etc... because you've never encountered what is otherwise routine and frequently discussed and complained about, even here amongst your threadizen buddies.
Heck, I remember those days, way back when I first started out. Ah, memories!
March 9, 2017 at 7:40 am
Ah, those idealistic early days!
March 9, 2017 at 11:30 am
patrickmcginnis59 10839 - Thursday, March 9, 2017 6:27 AMSQLRNNR - Thursday, March 9, 2017 12:09 AMpatrickmcginnis59 10839 - Tuesday, March 7, 2017 12:41 PMGiven the critical level of importance related to permissions, one may think it is a concept that is well understood by all who are given the charge of protecting the data.
lol new to IT are we?
Not seeing how you made that conclusion.
The implication is that if you are brand new to IT, even having a great set of credentials, yet have never encountered those who might lack critical thinking skills and have only associated with those who neglect nothing on the way to best practices and theoretical purity, ie., you've never encountered the real world, then you "may think it is a concept that is well understood" etc etc... because you've never encountered what is otherwise routine and frequently discussed and complained about, even here amongst your threadizen buddies.
Heck, I remember those days, way back when I first started out. Ah, memories!
Yeah there are a lot of people out there that think they understand this basic concept around security. The article goes through how bad some of those practices SUCK! They never bother to learn the concepts around security and the difference between "deny" and "grant".
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply