Systems Engineering and RDBMS

Archive for July 21st, 2008

SOX – Tracking properties for the SQL Server Login Accounts

Posted by decipherinfosys on July 21, 2008

Here is a simple SQL to track the information on the login accounts (Windows as well as SQL Server accounts):

name as login_name,
type_desc as login_type,
LOGINPROPERTY(name, ‘PasswordLastSetTime’) as Pswd_last_Set,
LOGINPROPERTY(name, ‘IsLocked’) as Locked_Account,
LOGINPROPERTY(name, ‘IsExpired’) as Expired_Login,
LOGINPROPERTY(name, ‘IsMustChange’) as Must_Change_On_Login,
LOGINPROPERTY(name, ‘HistoryLength’) as Account_Tracked_For,
LOGINPROPERTY(name, ‘LockoutTime’) as Date_Account_Locked_Out
from sys.server_principals
where type in (‘S’, ‘U’)
order by type

It uses the function LOGINPROPERTY() and lists out the different login names, their type and then selective properties that you are interested in tracking.  For SOX compliance as well as SAS70 requirements, it is good to have this SQL run every now and then in order to collect the information.  Also, there could be alerts set up based on the data set that is returned in order to alert the admin about expired/locked accounts etc.

Posted in SQL Server | Leave a Comment »

Looking at all the SQL Server Instances in the network

Posted by decipherinfosys on July 21, 2008

In SQL Server, one way to look for the instances that one can connect is to simply run:

osql -L or sqlcmd -L

which will list out all the instances on the network that you can look at. You can also look at this through the GUI as shown below:

However, when doing this I noticed that one of the servers that I was looking for was not available in this list. I knew the IP address of the box and it was a default instance so I was able to connect through fine using the credentials but it set me thinking why would that particular instance not be available in the list. Then, I terminal serve’d into that box and noticed using the server configuration manager that we can chose not to expose the name of the instance if we do not want to. In the image below:

You can see that, the the “Hide Instance” property was set to Yes. Once that was changed to “No”, I was able to see the instance in the browse list as well as using osql -L. This, however tells us that if we want, this is yet another way to secure a SQL Server instance on the network.

Posted in SQL Server | Leave a Comment »