October 11, 2018 at 11:12 am
Currently we have 1 DB User mapped to a unique SQL Login. The way the software vendor (that makes the accounting software we use) wants security setup is to have a single SQL login tied to a DB User and hat DB User is the Database Owner and everyone that logs into the accounting software is in effect connecting as this same DB User/SQL Login. So if I named the SQL Login ACCTSOFTWARE and 100 users were using the accounting software then in the back end (i.e. when using some kind of monitoring ) I would see 100 instances of ACCTSOFTWARE. This of course is bad security practice and is why we have not done this for the last decade that weve been using this software. Unfortunately we are now faced with having to change because we are moving the DB to the vendors Cloud and they will not allow us to have multiple SQL Logins.
Currently we have a number of table audits in place that are possible only because we have each DB user tied to a unique SQL login so we can capture certain actions/change (i.e. AFTER TRIGGERS on table) using for example Original_Login().
My question is this, is there a way to so this where you have only 1 SQL Login but multiple DB users? Its my understanding that each SQL Login can be tied to a single DB user but I may be wrong about that.
I know this setup of 1 DB User that all users connect as is very poor security but there is nothing we can do and so I'm trying to find work-a-rounds to this.
Kindest Regards,
Just say No to Facebook!October 11, 2018 at 11:50 am
Does this mean that the software package manages the permissions of each user in the system? Does the software package have any means to audit the work being done by each user?
October 11, 2018 at 12:36 pm
Lynn Pettis - Thursday, October 11, 2018 11:50 AMDoes this mean that the software package manages the permissions of each user in the system? Does the software package have any means to audit the work being done by each user?
Within its own framework it does manage security but not like you would think. Each user logs in as the DBO user at the DB level but within the application each user is different (diff name & PWD). Problem is its superficial; every connection to the DB be it a real person or a process (external facing connections) they all come in as the single DBO user. What little auditing it does is full of holes and does not properly audit changes to every table . In fact it does little auditing. Its a flaw that has supposedly been addressed in the next major iteration of the product which we will be moving to but not for possibly year and so until then I'm trying to find a solution/work-a-round to the existing work-a-rounds they are no disallowing.
Kindest Regards,
Just say No to Facebook!October 11, 2018 at 2:22 pm
YSLGuru - Thursday, October 11, 2018 12:36 PMWithin its own framework it does manage security but not like you would think. Each user logs in as the DBO user at the DB level but within the application each user is different (diff name & PWD). Problem is its superficial; every connection to the DB be it a real person or a process (external facing connections) they all come in as the single DBO user. What little auditing it does is full of holes and does not properly audit changes to every table . In fact it does little auditing. Its a flaw that has supposedly been addressed in the next major iteration of the product which we will be moving to but not for possibly year and so until then I'm trying to find a solution/work-a-round to the existing work-a-rounds they are no disallowing.
Wow...that is bad. And it's the accounting system. Any chance the front end is not going through an app or web server?
If the access is from their PCs, you'd have the connection IP, host to work with.
Sue
October 11, 2018 at 3:24 pm
YSLGuru - Thursday, October 11, 2018 12:36 PMWithin its own framework it does manage security but not like you would think. Each user logs in as the DBO user at the DB level but within the application each user is different (diff name & PWD). Problem is its superficial; every connection to the DB be it a real person or a process (external facing connections) they all come in as the single DBO user. What little auditing it does is full of holes and does not properly audit changes to every table . In fact it does little auditing. Its a flaw that has supposedly been addressed in the next major iteration of the product which we will be moving to but not for possibly year and so until then I'm trying to find a solution/work-a-round to the existing work-a-rounds they are no disallowing.
I've seen this kind of setup before, unfortunately. The system probably requires 1 login so that the application server can connect to the database and use connection pooling capabilities, but that login being DBO is a very poor choice. It probably can't have separate logins for each user because of
To answer your question, there is not a way for SQL Server to "split" a single SQL authenticated login by multiple users, how would it determine which one to connect to the database as? I tried it in a sandbox database I have and it generates the error:
Msg 15063, Level 16, State 1, Line 8
The login already has an account under a different user name.
October 12, 2018 at 1:19 am
YSLGuru - Thursday, October 11, 2018 12:36 PMWithin its own framework it does manage security but not like you would think. Each user logs in as the DBO user at the DB level but within the application each user is different (diff name & PWD). Problem is its superficial; every connection to the DB be it a real person or a process (external facing connections) they all come in as the single DBO user. What little auditing it does is full of holes and does not properly audit changes to every table . In fact it does little auditing. Its a flaw that has supposedly been addressed in the next major iteration of the product which we will be moving to but not for possibly year and so until then I'm trying to find a solution/work-a-round to the existing work-a-rounds they are no disallowing.
This is a pretty standard use case. We have a single user (not dbo) per app that the API uses to connect to the DB. The App maintains a list of users in a table, and passes the UserID to every proc to identify the user that is using the system. Creating and managing new SQL or AD users for a publicly facing application would be a nightmare - We get ±40k new users per month.
October 12, 2018 at 7:15 am
Chris Harshman - Thursday, October 11, 2018 3:24 PMYSLGuru - Thursday, October 11, 2018 12:36 PMWithin its own framework it does manage security but not like you would think. Each user logs in as the DBO user at the DB level but within the application each user is different (diff name & PWD). Problem is its superficial; every connection to the DB be it a real person or a process (external facing connections) they all come in as the single DBO user. What little auditing it does is full of holes and does not properly audit changes to every table . In fact it does little auditing. Its a flaw that has supposedly been addressed in the next major iteration of the product which we will be moving to but not for possibly year and so until then I'm trying to find a solution/work-a-round to the existing work-a-rounds they are no disallowing.I've seen this kind of setup before, unfortunately. The system probably requires 1 login so that the application server can connect to the database and use connection pooling capabilities, but that login being DBO is a very poor choice. It probably can't have separate logins for each user because of Kerberos double hop issues and the complexity of impersonation.
To answer your question, there is not a way for SQL Server to "split" a single SQL authenticated login by multiple users, how would it determine which one to connect to the database as? I tried it in a sandbox database I have and it generates the error:
Msg 15063, Level 16, State 1, Line 8
The login already has an account under a different user name.edit: I seemed to stop mid sentence, added comment about kerberos double hop
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply