If you're having security issues ranging from monitoring failed logins, restricting account access or even a permissions issue, this collection of frequently asked questions will help. Our experts at SearchSQLServer.com have addressed readers' common questions, which should assist you in performing tasks and resolving issues.

Sadly, failed login error reporting is disabled by default (gasp!). I don't know why Microsoft has it setup that way, but it is. Pop into SQL Enterprise Manager, choose your server, go to properties, go to the Security tab and enable it. Once this has been activated, you'll start seeing them show up in your log files. A note of caution, the errors are notoriously brief and don't provide the amount of information that I would want if I was performing forensics on the server. Steven Andres, Security Expert

SQL Server is a data repository and not a full access control system. Your database application should be the one in charge of controlling access to the back-end data. One popular way that I've seen this performed is by having the application login using one set of credentials and then managing the user credentials inside your custom application. This way, when a user attempts to login concurrently, your application can query a "currently logged in" users list and display the appropriate error message. The user's password won't allow him into Query Analyzer so there's no harm in attempting a connection. Steven Andres, Security Expert

Can you explain the differences between Data Transformation Services (DTS) and SQL Server Integration Services (SSIS)?

SQL Server stores LOGIN information in the MASTER database and USER information in the user databases. Because of this, when you restore a database from a different server or if you attach a database from a different server, you will get mismatches unless the servers were set up and administered the same way.

If you are moving a database to a brand new server where no login information yet exists, you can script out login information and apply it to the new server. This can be done using DTS or it can be done by writing T-SQL code to script out the data. This MSDN article goes into more detail on how this can be accomplished.

Also refer to sp_change_users_login in SQL Server Books Online. This stored procedure gives you information on mismatches as well as allows you to fix the mismatched data.

To identify and clean up orphaned users, you can write T-SQL code that will identify the users that do not have a corresponding login and then use the sp_revokedbaccess stored procedure to drop them. Refer to this article for a completed solution. Greg Robidoux, Backup and Recovery Expert

In SQL Server 7 and 2000, when you create an object owned by a user other than DBO, you have to qualify the object by owner name when accessing it, unless you are logged in as that user.

In SQL Server 2005, objects are no longer qualified by owner. Instead, they're qualified by schema name. This is something I covered in a recent SearchSQLServer.com tip on SQL Server 2005 permissions. In essence, this will result in less confusion regarding object qualification; you will be able to organize objects logically, rather than by who owns them.

As for server logins, I don't see a lot of potential issues. The same semantics apply in terms of what logins are to SQL Server and how they work. One difference you might want to be aware of is SQL Server 2005's ability to enforce password policies, which may mean that some of your users will have to change their passwords after your upgrade. Adam Machanic, SQL Server 2005 Expert

The System Administrator account, by definition, has access to everything at all times. You could reduce that access, but then you are changing the definition of the "SA" account and you should really have your application (or users) logging in as another username. Steven Andres, Security Expert

Use OSQL with the –E option (for authenticated user). If you are an administrator of the OS, you will get in without being prompted for the sa password. Once you are at the OSQL interactive prompt, issue the following command:

If you have logging enabled, you should be able to see the new account created. However, by default there is very little SQL logging enabled (a deficiency I hope is rectified in SQL 2005). Steven Andres, Security Expert

Sometimes, there is a good reason for this. Some developers make their software so that it can be deployed on a number of database back-end solutions (MS SQL, MySQL, ORACLE, IBM DB2, Sybase, etc.). For this reason, they would rather write their authentication procedures once, in their compiled software, and be done with it.

Of course the "right" way to do it would be to have the database back-end manage authentication, but this turns into a support nightmare for the developer. Take it from someone who's been there -- if you do it the "right" way, you'll end up spending most of your valuable tech support engineers' time supporting MS SQL or ORACLE or DB2 issues, when they really should only be supporting the application itself. It is tempting to just say, "Well, that's a Microsoft/Oracle/IBM problem" if you're that tech support engineer. But in the end you must support your customer. This means that now all your support engineers have to become experts in five different database packages -- that's not going to happen! Steven Andres, Security Expert

I am writing a program with VB.NET to access some data from my SQL 2000 Server but it always complains of log in failed for user "myservernameaspnet." What could be causing this error and do you have a solution?

It sounds like you might have a permissions issue with the ASPNET user on your development box. A simple way of checking is to grant "full control" permissions to the "ASPNET" user in the directory where your data resides. If this works, you know you have a permissions issue. Start rolling back the permissions until it breaks again -- you want to have the minimum amount of permissions necessary for the program to run properly. If this doesn't work, try re-installing ASP.NET. Steven Andres, Security Expert

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy