LOGIN

Who’s been logging into my SQL Server?

Knowing who has been logging into your SQL Server is one of the key things to know about your SQL Server.  It lets you know quite a bit about who’s using your server, and about who’s been trying to break into your SQL Server, and most importantly if they have succeeded.

Obviously you can setup a SQL Profiler trace to capture this information but that requires the overhead of running SQL Profiler, and who wants that.

All version of SQL Server (from 2000 and up at least) provide some level of logging about who has tried to log into the SQL Server.  Within Enterprise Manager or SQL Server Management Studio’s Object Explorer right click on the Server and select properties (if using Enterprise Manger select properties not connection properties).

Select the Security Tab and find the Login Auditing section.  By default SQL Server only logs the failed logons which is good as it tells you who hasn’t been able to log into the server.  However it doesn’t tell you it they have been successful which is why you may want to change this to both failed and successful logins.

Now changing this setting has an upside and a downside.  The upside is that you know who has been successfully broken into your database using a brute force attack and when.  The downside is that every client that successfully connects to the SQL Server will also log an entry, making it very hard to find the correct entry you are looking for.

Where do these entries get logged to you ask?  That’s the other downside.  They get logged to the SQL Server ERRORLOG file and the Windows Security log file.  Which means that these files will fill up fast.  And if you have a large enough client base logging into the database VERY FAST.

In a perfect world, I’d set this screen to both failed and successful logins.  In reality failed is probably all I can do.

SQL Server 2000 didn’t provide a whole lot of information about what is happening as it only says that Login n has tried to connect and failed.  Not exactly helpful as you don’t know who was trying to login to the SQL Server using the sa account over and over again.  SQL Server 2005 and up include a little piece of helpful information, the IP Address of the person who tried to connect to the SQL Server.  This will help tell you who is connecting to the SQL Server so that you can smack them around.

Denny

Back To Basics: Logins and Users, what’s the difference?

Usually Logins and Users are words which are interchangeable with each other.  However in Microsoft SQL Server they are very different things.  Because everyone assumes that they are the same thing, it can get a little confusing.

Logins are created at the database server instance level, while uses are created at the database level.  In other words logins are used to allow a user to connect to the SQL service (also called an instance).  You can have a login defined without having access to any databases on the server.  In this case you would have a login, but no users defined.  The user is created within the database and when it’s created is mapped to a login (users can be created without mapping them to a login, but we’ll talk about that at some point in the future).  This mapping is what allows the person connecting to the instance to use resources within the database.

If the login was created directly within the database, each database would have to keep track of the usernames and passwords of everyone who needed access to the database, which would cause a security nightmare.  Using the login in each database idea, lets create a login in each database called user1.  We set the password for user1 the same on all the databases on the server.  We then backup the database, change the password for that user on all the databases, then restore the database.  We now have an out of sync password for a single database on the server.

 Because of this mapping between logins and users, if you create a SQL Login on your server and grant it rights to a database via a user then backup the database, and restore the database to another server after creating a login on the second server with the same name.  You would think that the login would have access to the database.  However you would be wrong.  This is because the SID of the login and the user are different.  You have to use the sp_change_users_login procedure to sync the user with the login.

Denny