January 10, 2008 at 8:21 am
This is why my previous suggestion comes in handy. Change the sa password and keep it at a safe place ! If anything else that us sa password break, be prepare to create new account and change over the user account to a new account.
sopheap
January 10, 2008 at 8:26 am
Technically, nothing should be using the SA account. If you have jobs, processes, users or anything else using the SA account, you've already got a serious security problem.
SA should only be used in emergencies, kind of like the new DAC account. Everything else should be owned by or use other accounts that have exactly the permissions needed for that process/job/object and no more.
And, as others have said, if you truly need sysadmin privledges, grant it to another account and use that instead of the SA account.
January 11, 2008 at 12:15 am
Did I mention we change sa-password at all servers at least every month (and most of the time the new one is a guid).
Yes we do.
All dba / sysadmins connect using windows authentication.
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
January 11, 2008 at 7:35 am
My company's policy is that no one, ever, gets to use sa. Software that claims to requrie SA is either rewritten by the vnedor to use a dbo ID, or is taken off the list of approved applications. I can't think of a reason why any vendor provided application needs sa.
In our environment, if there is a business case for a user to have sysadmin rights on a server, it is granted to an ID separate from the user's everyday LAN ID. This prevents stupid mistakes.
January 11, 2008 at 9:50 am
Hello all! Thanks for the responses I will definately pdf this thread! By the way, today the MAS500 consultants got this response from the MAS500 people:
"If the customer does not want to use the SA user for MAS 500 functions, they can use the ADMIN user, which if it has not been altered should have the same rights as SA. If any changes have been made to the ADMIN user they will need to assign proper rights.
We would like to remind though that our datafixes exec in SQL do require to be compiled with the SA user.
Kind regards,
xxxxx xxxx
CS Analyst, MAS 500"
What does this mean? SA is required for datafixes exec in SQL.
edit: took out the Analyst's name.
January 11, 2008 at 10:07 am
What it means is they've packaged their datafixes in an .msi or something similar which has the SA account hardcoded into it and it just prompts for a password.
I've seen similiar things to this before. The SA account is the only account guarunteed to be on an end user's SQL Server, so they try to use it for all the program install needs. This obviates the need for the install techs to run around going crazy trying to invent new accounts.
I personally don't agree with it, but I do understand the reasoning behind it.
January 11, 2008 at 10:29 am
I recommend keeping each application on its own database server with a unique SA password. Sharing hardware and SQL Server instances works, but makes identifying and isolating security as well as performance problems extremely difficult to isolate. Having everything seperate on lots of small servers is scalable and is a more secure infrastructor. Consultants are only given access to the servers and application they are servicing and when they leave the passwords are change immediatly. Audits of exiting accounts and permissions should also be done on regular basis, but definitely when a consultant leaves. A common hack is to leave a back door by granting permissions to an exising user or create a new user so future requests for an SA password is not needed, however, this could leave your system open to unwanted access. In most cases this is not a malicus act, they just want to make it easier to service your system later. However, having to supply a password each time the vender accesses the system is OK because you know when they are accessing the system and you are secure because each database server uses a different SA password.
January 11, 2008 at 11:42 am
Thanks Brandie. Then I must expect that installs or any upgrades to MAS500 will entail using the sa account and just deal with it by not giving the password out, but having us type it in when the time comes to do so. My understanding with her email is that sa does not need to be used anytime else (i.e. while using the application).
I viewed our "admin" SQL account just now and it is setup like this:
Server role shows "System Administrator" . Database access is limited to only the MAS500 databases and Permit in DB role is public, db_owner, ApplicationDBRole (which seems to be a customized entry)
What is the default settings for the sa account in the security section of SQL Server Enterprise Manager? Especially in the section called "Permit in Database Role"? I just want to make sure it wasn't changed and need a template to measure against. We have SQL 2000.
Thanks again everyone.
January 11, 2008 at 12:43 pm
I'm going to have to get back to you on the "permit in database role" stuff for SA, but I wanted to mention something to you about your last post.
If the Admin account they are using for the MAS databases is SysAdmin, it doesn't matter whether or not they have limited database access. Sysadmin overrides this and gives them access to everything.
My advice is to remove all fixed server roles from this account and make it DBO in the MAS databases. This will give it permissions to do everything in those databases & nothing in any other database.
Also, if they're having to make manual server changes that require SysAdmin access, you should question why. Server related changes shouldn't be done without your permission or knowledge. Especially if you have other databases running on the same server as the MAS databases.
January 11, 2008 at 12:57 pm
I'll also add that the vendor should not be doing anything to a production server that hasn't been tested thoroughly on a test server. If your company is subject to Sarbanes Oxley, this is a basic requirement. The vendor should be able to give you a set of scripts and update routines that you run, rather than giving an outsider access to your production servers. All changes should be formally accepted by the users before you do anything.
we use a three tier process where all new code we write, plus any new programs from vendors, are tested on a development server. Once we are satisfied, we migrate the code to a test server, where the users run their own tests. Once the users accept the changes, the DBA's migrate the changes to the production servers. This pretty much prevents major surprises.
January 11, 2008 at 4:04 pm
"My advice is to remove all fixed server roles from this account and make it DBO in the MAS databases. This will give it permissions to do everything in those databases & nothing in any other database. "
Taking your advice, I just unchecked "System Administrator" in the server role area and now there is nothing checked in this area. I left the rest untouched. Going to sit and wait. If everything goes to crap then I guess I should put it back and demand MAS500 why.
Thanks and will await your response on the default sa settings.
And Ross, MAS500 developers are not developing directly on our SQL server. However should there be a "patch" or update for the software, it is applied directly to the live program and/or db's. This is where the MAS500 consultant, specifically the "engineer", comes in to do this.
I know they have a group of "test" databases and "live" databases in the SQL server. They had used the test db to initially hash out the initial setup of MAS500 for our company and tested some input into it. Since the test database is not an exact replica of the live database, the fixes or patches get applied to the live database directly--probably because our accounting people want things fixed ASAP. I wish they would at least make a backup of our live data before they begin...
January 12, 2008 at 10:44 am
kash_marsh (1/11/2008)[hr
Thanks and will await your response on the default sa settings.
I wish they would at least make a backup of our live data before they begin...
sorry to butt in.........default settinf for SA is 'server admin role' set in server roles. this gives carte blanchen so nothing else is ticked.
I strongly advise you start backing up the database just before upgrades. I don't see how anyone can complain about that!
---------------------------------------------------------------------
January 14, 2008 at 4:03 am
Adding to what George said, if you have the SA pwd, then you definitely have the ability to back up your own databases. I would assume the MAS people call you before they come over. Right? Set up a backup job that you can manually run at will and kick it off right before they show up. Then you've got your backup just in case.
If they don't notify you in advance, make them sit and wait while you do the backup. After all, better safe then sorry. Especially if they're putting in patches in the middle of the business day.
January 14, 2008 at 6:27 am
Applying patches without any testing is really bad, and will, at some point, lead to severe problems. We've been with our vendor for over 20 years, have a great relationship with them, but still don't apply any updates without thorough testing. Our test systems mirror our production systems, and are refreshed as needed to properly test updates and fixes.
Accountants always want things fixed immediately. You have to be strong enough to tell them that no untested fixes will be applied to production systems. I've been doing this for almost 20 years, as an accountant and as a support person, and haven't seen an issue yet where the fix couldn't wait for testing. Develop a change management process, and ensure that management understands how important the entire process is, both for reliability, and for compliance.
January 15, 2008 at 12:06 pm
george sibbald (1/12/2008)
kash_marsh (1/11/2008)[hr
Thanks and will await your response on the default sa settings.
I wish they would at least make a backup of our live data before they begin...
sorry to butt in.........default settinf for SA is 'server admin role' set in server roles. this gives carte blanchen so nothing else is ticked.
I strongly advise you start backing up the database just before upgrades. I don't see how anyone can complain about that!
Strange. When I open the sa account, there's an error.
Microsoft SQL-DMO error.
"Error 21776: [SQL-DMO] The name "dbo" was not found in the Users collection. If the name is a qualified name, use [] to separate various parts of the name, and try agan."
There is an OK button.
I can continue into the account and the "System Administrator" box was not checked for some reason. So I checked it.
So, what is this error and what can I do to correct it? Is this something the consultants could have done by accident?
Thanks in advance.
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply