April 24, 2018 at 1:41 pm
Lynn Pettis - Tuesday, April 24, 2018 1:32 PMEric M Russell - Tuesday, April 24, 2018 12:31 PMcaroline-599293 - Tuesday, April 24, 2018 12:43 AMInstalled SAP, did you? 😉or People, or Pegasystems, or ...
I believe the root problem with turnkey style ISV databases, especially the CRM related ones, is that it's marketing folks, not technical folks, who are driving the product development. Don't waste your time boring them your geeky opinions about software and database engineering; what they care about is market share and selling the next big client implementation.I helped support PeopleSoft HR and Finance systems, each of which had 55,000+ tables. Of course, not all were used because we didn't use all of the modules available in the products.
I believe these products benefit from momentum; there are just so many Fortune 500 and government deployments, high profile endorsements, and product placements in trade publications that it's perceived as being the defacto standard for a specific industry. It's not that the product works well by any objective measure or relative to other potential options, it's just that it reliably gets the job done, assuming you throw enough hardware and dedicated IT staff at it.
It's a multi-billion dollar industry where once per decade a new giant will topple the previous giant. But the next time the industry gets disrupted, it might just be a medium sized cloud based provider offering a better engineered solution.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 25, 2018 at 4:01 am
And, having worked with numerous third party packages I'm supposed to be surprised by this?
"We need sa with a blank password, that's how it's hardcoded to connect, and a domain admin with AppName , AppName as the name and password".
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 25, 2018 at 7:34 am
andrew gothard - Wednesday, April 25, 2018 4:01 AMAnd, having worked with numerous third party packages I'm supposed to be surprised by this?
"We need sa with a blank password, that's how it's hardcoded to connect, and a domain admin with AppName , AppName as the name and password".
Wow, do they also suggest you leave some milk and a plate full of cookies for the hackers? :ermm:
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 25, 2018 at 7:35 am
andrew gothard - Wednesday, April 25, 2018 4:01 AMAnd, having worked with numerous third party packages I'm supposed to be surprised by this?
"We need sa with a blank password, that's how it's hardcoded to connect, and a domain admin with AppName , AppName as the name and password".
Heh...yeeeaaahhh... that's when I tell management that I was looking for a job when I found this one. I won't ever let that happen, period, no exceptions. Very fortunately, I have a few Directors/VPs that get that and back me up 100% on that.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 25, 2018 at 7:47 am
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 25, 2018 at 7:56 am
Eric M Russell - Wednesday, April 25, 2018 7:47 AMIf a software application is hard coded to use the "SA" account, you can rename it and then create a new account named "SA" with limited permissions. One or more builtin database level roles like db_ssisadmin, db_datareader, or even db_owner can provide all the permissions it requires to function. Anyone stupid enough to design an application with a hard coded connection to the 'SA' account is probably too stupid to realize you've switched sysadmin role membership out from under them.
The way I look at it, if someone is so stupid as to hardcode "SA" in their connection, then I'm not going to trust their application in any way, shape, or form.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 25, 2018 at 8:12 am
Jeff wrote:
The way I look at it, if someone is so stupid as to hardcode "SA" in their connection, then I'm not going to trust their application in any way, shape, or form.
——
Agreed. The developer is either stupid, lazy, impatient or nefarious.
Stupid: cannot figure out how to do the work without sysadmin permissions
Lazy: knows it can be done but does not want to expend the effort
Impatient: knows it can be done and knows how but wants the result and does not care how irresponsibly it is obtained.
Nefarious: codes it with SA to use it as a back door to do bad things. Imagine an app that has a hidden function to mimic SSMS and also hard codes a connection via SA. Now the app user has a back door into production SQL. Dangerous. “Select name,address,city,state,zip,ccnumber,expdate from CustPaymentHistory”
Or if disgruntled
“Alter database ERP set single_user with rollback immediate; drop database ERP;”
This is why, even if you have to keep sql login security active, you should change the SA password to a 63 character random string from grc.com/pass and then disable it. If you need sysadmin privileges, then create another account named “janitor” or something innocuous and give it a long nasty password as well.
Thanks
John.
April 25, 2018 at 1:13 pm
Jeff Moden - Wednesday, April 25, 2018 7:56 AMEric M Russell - Wednesday, April 25, 2018 7:47 AMIf a software application is hard coded to use the "SA" account, you can rename it and then create a new account named "SA" with limited permissions. One or more builtin database level roles like db_ssisadmin, db_datareader, or even db_owner can provide all the permissions it requires to function. Anyone stupid enough to design an application with a hard coded connection to the 'SA' account is probably too stupid to realize you've switched sysadmin role membership out from under them.The way I look at it, if someone is so stupid as to hardcode "SA" in their connection, then I'm not going to trust their application in any way, shape, or form.
I found that many times the only needs for elevated permissions include:
1 - installation/update (usually of the app code)
2 - permission to "truncate table", (or create, drop, alter, ...)
3 - refusal to use domain accounts (i.e. insist on SQL Local Accounts)
4 - requirement to access local/remote file systems and/or other resources for SSIS, ETL, etc.
5 - xp_CmdShell (ugh).
Rather than figure out how to use A/D, NTFS, SQL, and Windows security they just default to "I need Admin, and I need it EVERYWHERE. Then I won't have any problems with security".
Usually a little discussion will at least get us to domain accounts with some limitations on the permissions.
If the vendor hard-coded connection strings then any standards based audit (e.g. HIPAA, SOX) should disqualify them anyway.
April 25, 2018 at 1:58 pm
April 25, 2018 at 4:14 pm
Ray Herring - Wednesday, April 25, 2018 1:13 PMJeff Moden - Wednesday, April 25, 2018 7:56 AMEric M Russell - Wednesday, April 25, 2018 7:47 AMIf a software application is hard coded to use the "SA" account, you can rename it and then create a new account named "SA" with limited permissions. One or more builtin database level roles like db_ssisadmin, db_datareader, or even db_owner can provide all the permissions it requires to function. Anyone stupid enough to design an application with a hard coded connection to the 'SA' account is probably too stupid to realize you've switched sysadmin role membership out from under them.The way I look at it, if someone is so stupid as to hardcode "SA" in their connection, then I'm not going to trust their application in any way, shape, or form.
I found that many times the only needs for elevated permissions include:
1 - installation/update (usually of the app code)
2 - permission to "truncate table", (or create, drop, alter, ...)
3 - refusal to use domain accounts (i.e. insist on SQL Local Accounts)
4 - requirement to access local/remote file systems and/or other resources for SSIS, ETL, etc.
5 - xp_CmdShell (ugh).
Rather than figure out how to use A/D, NTFS, SQL, and Windows security they just default to "I need Admin, and I need it EVERYWHERE. Then I won't have any problems with security".Usually a little discussion will at least get us to domain accounts with some limitations on the permissions.
If the vendor hard-coded connection strings then any standards based audit (e.g. HIPAA, SOX) should disqualify them anyway.
I agree with everything except the disdain for xp_CmdShell. Done correctly, xp_CmdShell requires neither elevated privs nor the normal fear that surrounds it. It's almost as easy to do correctly as it is to give a stored procedure privs to TRUNCATE a table while the user only has PUBLIC privs and cannot use TRUNCATE directly. They only have privs to execute the stored procedure that uses it.
I will, however, agree that most applications that require xp_CmdShell have been written incorrectly to use it and, for that, I share the disdain to an extreme. That's a very large reason why I won't let a 3rd party app go with "sa" privs. The only exception that I'll sometimes allow is the monitoring software from certain well known vendors.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 25, 2018 at 4:17 pm
p.s. I'm going through the drill right now. Our telephone system vendor has asked for a login with "sa" privs for their new reporting system. They're into that "Well other people let us have one... our software requires it". Yeah, right. For a reporting system... I hope they know what "obese opportunity" means. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
April 25, 2018 at 11:58 pm
Jeff Moden - Wednesday, April 25, 2018 4:17 PMp.s. I'm going through the drill right now. Our telephone system vendor has asked for a login with "sa" privs for their new reporting system. They're into that "Well other people let us have one... our software requires it". Yeah, right. For a reporting system... I hope they know what "obese opportunity" means. 😀
LOL
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 26, 2018 at 1:30 am
This stuff is horrible, but I know why it's out there.
In the early days of SQL Server, Windows NT/2000 could log on with Windows Auth, but Windows 95/98/ME couldn't, so we were forced to use password based auth. To use anything other than the sa account meant having to have access to the server to magic up another account. This was a world of pain, considering most clients were small businesses and didn't have the first idea about how to do this, or really care. The days before remote desktop was a thing.
Add different versions of SQL Server to the equation.
So, most of this is zombie-ware from a bygone era (when Apple looked like it was going to go bust).
sa was the only sensible zero-configuration option. It all seems so innocent now.
That said, it's a travesty that proper options aren't supported for users who know what they're doing.
As they say, security = 1 / convenience!
April 26, 2018 at 1:38 am
ben.mcintyre - Thursday, April 26, 2018 1:30 AMThis stuff is horrible, but I know why it's out there.In the early days of SQL Server, Windows NT/2000 could log on with Windows Auth, but Windows 95/98/ME couldn't, so we were forced to use password based auth. To use anything other than the sa account meant having to have access to the server to magic up another account. This was a world of pain, considering most clients were small businesses and didn't have the first idea about how to do this, or really care. The days before remote desktop was a thing.
Add different versions of SQL Server to the equation.So, most of this is zombie-ware from a bygone era (when Apple looked like it was going to go bust).
sa was the only sensible zero-configuration option. It all seems so innocent now.That said, it's a travesty that proper options aren't supported for users who know what they're doing.
As they say, security = 1 / convenience!
mmmm ... no .... just sloppy development by software vendors ... They focus on their stuff just doing its job, not their stuff doing its proper job in a responsible and secure way.
as always I get to hear: "but you are the first complaining about this security thing ! We have never had issues with other clients."
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 26, 2018 at 1:45 am
ALZDBA - Thursday, April 26, 2018 1:38 AMas always I get to hear: "but you are the first complaining about this security thing ! We have never had issues with other clients."
THAT is the truly scary part.
Viewing 15 posts - 61 through 75 (of 87 total)
You must be logged in to reply to this topic. Login to reply