| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2003-05-27 : 17:26:51
|
| Hi, 1. How often do you guys change your 'sa' password or is it only when something bad happens which triggers a change?2. what is the recommended practice for changing the password?3. Is there any std procedure exisiting for pwd change?Thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-05-27 : 17:47:01
|
| 1) You should never use the sa login for anything (if you can avoid it - some third party apps still insist on using it), set a strong password (using a random password generator) and lock it away (even if server is using Windows authentication mode only)2) If sa login is used it's generally a third party app so it depends on the flexibility of the app as to how easy it is to change it's password as to how often you do it3) sp_password (see BOL for details)HTHJasper Smith0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27 |
 |
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-05-27 : 18:35:13
|
| Well we do use PeopleSoft which reqires you to login as 'sa' to get access first then it has it its own built-in security which reconnects you to the application with valid permissions.We use 'sa' to set up our ODBC (client/server) connections so every time we change 'sa' password, we have to reset the ODBC connections.As of now the password is complicated enough but i think I want to know even with all this in place, what is the recommended time-period of changing passwords on a microsoft sql server?thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-27 : 19:08:51
|
| I would say that the password should be changed when a DBA has left the company/division/group. No one should have the sa password except for the DBAs. We have only changed the password once since I have worked here (a little over a year now). It was changed because other groups had knowledge of the password.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-27 : 19:12:11
|
quote:
We use 'sa' to set up our ODBC (client/server) connections so every time we change 'sa' password, we have to reset the ODBC connections.
Why are you using the sa account for your connection?Tara |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-05-27 : 20:03:36
|
| Because PeopleSoft (PS) provides access to application (2-tier not web) only when access-id is 'sa'. Then it verifies from 3 tables your user-id and reconnects with appropriate permissions.in other words, you need 'sa' to get access to 3 security-tables which in turn give access to the entire application.There are a couple of PS tools (Query & SQR report writer) which require ODBCs and developers cannot run SQR unless you connect to PS as 'sa'. in our case, we are programmers cum dba(s) so our server admins know the password.thanks,sarat**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-28 : 10:52:52
|
quote:
Because PeopleSoft (PS) provides access to application (2-tier not web) only when access-id is 'sa'.
The connection still has god control over your database, unless you've change sa's authority.quote:
Then it verifies from 3 tables your user-id and reconnects with appropriate permissions. in other words, you need 'sa' to get access to 3 security-tables which in turn give access to the entire application.
That's the application front end. What's to prevent the locally installed ODBC driver being used with Access to connect to it?quote:
There are a couple of PS tools (Query & SQR report writer) which require ODBCs and developers cannot run SQR unless you connect to PS as 'sa'.
Why would that be the case. SQR report writer should be a read only (NOLOCK) operation. Is this against a OLTP or OLAP database?quote:
in our case, we are programmers cum dba(s) so our server admins know the password.thanks,sarat
You sure we're talking about sa?**To be intoxicated is to feel sophisticated, but not be able to say it.**[/quote]Brett8-) |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-05-28 : 17:14:11
|
| Bret,I haven't quite understood your questions but I will try to answer them.1. Our db is OLTP.2. SQR is also used to update/delete data, sometimes even to alter tbls.I didn't understand "SQR report writer should be a read only (NOLOCK) operation"! Please elaborate.3. ya, it is 'sa' but PS has additional security so we aren't that worried. Only 5 ppl have access to Query analyzer as testers & developers & only 2 of us are sa(s).4. What do you mean by "What's to prevent the locally installed ODBC driver being used with Access to connect to it? " - I mean how is it a threat??Anyway, maybe this explanation helps:ACCESS IDWhen you create any User ID, you must assign it an Access Profile, which specifies an Access ID and password.The PeopleSoft (PS) Access ID is the RDBMS ID with which PS application(s) are ultimately connected to your database once the PS System validates the User or Connect ID. An Access ID typically has administrator-level database access; that is, it has all the RDBMS privileges necessary to access and manipulate data for an entire PeopleSoft application. The Access ID should have SELECT, UPDATE, and DELETE access.It's important to understand that users do not know their corresponding Access IDs. They simply signon with their User or Connect ID and—behind the scenes—the system logs them onto the database using their Access ID. Should they try to access the database directly with a query tool using their User or Connect ID, they wouldn't get far. User and Connect IDs only have access to the few PS tables used during signon, and that access is SELECT-level only. Furthermore, PS encrypts all sensitive data that resides in those tables.----Thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-28 : 18:13:49
|
| The explanation of ACCESS ID doesn't mean that you have to use the sa account. Just create another account that has the minimum amount of privileges (according to what you have posted it would just be SELECT UPDATE DELETE) for PS. If PS needs more access, then add that access. PS should not be using the sa account. This other account can be given dbo permissions if needed.Tara |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-05-29 : 13:37:46
|
| I agree with you. PS does require an id with 'dbo' priveleges because we build tables, views within PS application. PS recommends using 'sa' though - i think i will go back and ask them why!may be the odbcs and other client tools can also use the 'dbo' id instead of 'sa'.We created all our tables as 'sa' so our objects are named as PRODDB.dbo.PS_Tablename. If I with a role of 'ddladmin' create these objects now, wouldn't they have my name (winnt userid) attached to the tables intead of dbo - that may be a problem for us!Thanks,Sarat**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-29 : 13:39:14
|
| If your account is a member of the db_owner role, then the object will be owned by dbo and not your own account.Tara |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-05-29 : 14:11:12
|
| I just did a small test. I changed by login id where I gave dbowner role for a particular db. then i logged into QA and created a table by "select * into PS_SC from PS_TBA". I went into EM as 'sa' and saw that the table has been created with my login id and not dbo. I did check in the dbowner role and my id was listed along with dbo and user associated with dbo is 'sa'.thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-29 : 14:25:19
|
| BOL says (found in CREATE TABLE article):"ownerIs the name of the user ID that owns the new table. owner must be an existing user ID in the database specified by database_name. owner defaults to the user ID associated with the login for the current connection in the database specified in database_name. If the CREATE TABLE statement is executed by a member of the sysadmin fixed server role, or a member of the db_dbowner or db_ddladmin fixed database roles in the database specified by database_name, owner can specify a user ID other than the one associated with the login of the current connection. If the CREATE TABLE statement is executed by a login associated with a user ID that has only create table permissions, owner must specify the user ID associated with the current login. Members of the sysadmin fixed server role, or logins aliased to the dbo user are associated with the user ID dbo; therefore, tables created by these users default to having dbo as the owner. Tables created by any logins not in either of these two roles have owner default to the user ID associated with the login."Your table should have been owned by dbo. Not sure why it isn't. Something must be wrong with the account. The account probably isn't aliased to dbo.TaraEdited by - tduggan on 05/29/2003 14:27:05Edited by - tduggan on 05/29/2003 14:29:07 |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2003-05-29 : 19:06:47
|
quote:
Your table should have been owned by dbo. Not sure why it isn't. Something must be wrong with the account. The account probably isn't aliased to dbo.
Just wanted to point out that aliasing a login to dbo and adding a user to db_owner role are two different things. Aliasing is a legacy method of managing security (see sp_addalias in BOL). So yes, Tara, as you pointed out, the login Sarat created was never aliased to dbo. Instead, Sarat created a separate database user for that login, and added the user to the db_owner role, thereby granting him the same set of priviledges that the dbo user has. This is a key point: his user isn't dbo, but has the same set of rights. So if his user creates an object in the database, he will become the owner of that object by default. If he wants to create an object, owned by someone else, he needs to specify the desired owner. So in Sarat's example, if he wants the new table to be owned by the dbo, he needs to say so:select * into dbo.PS_SC from PS_TBA Alternatively, he could alias his login to dbo user by running sp_addalias. Then every time he connects, using that login, he will effectively impersonate dbo user. Therefore, he wouldn't need to specify object ownership anymore. On the downside, creating aliases makes the task of auditing moree difficult (I think that's the main reason why Microsoft wants to do away with them). I hope this clears up some confusion, if there was any.  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-29 : 19:55:37
|
| Thanks izaltsman. I thought that sp_addalias had to be run, but since I have never run it (haven't needed to since the developers here type dbo. when creating objects), I didn't want to offer that up.Tara |
|
|
|
|
sa password change frequency