Thursday, January 31, 2008

Creating a Credential in SQL 2008 Server

Introduced in SQL Server 2005 Credentials is a new security feature on top of the security
policies to address the issue of privileges one can have while accessing a resource outside the
boundary on the network. Net Framework makes the outside access very easy with procedural code and credentials provide the security backup. This is continued in SQL 2008 as well. This short note shows how you may create credentials in SQL Server 2008 .

Step 1:
In MS SQL Server Management Studio expand the Security node and right click on the Credentials folder and from the pop-up click on New Credentials... as shown.

This opens up the New Credential window. You provide a name for this, for this note
' mysorian' is used.

Click on the ellipsis button along the field 'Identity'. The identity has a login to the local
server. This opens the Select User or Group window. The Object Types button will bring up the accessible Object Types. Here the USER or Built-in security prinicpal has been brought in. The location is the local computer. Insert a local identity such as the one shown. Click on the OK button.

This will bring you back to the New Credential window. Now provide a password and confirm it by entering it again, it better be strong. If you need to encrypt this, you may choose to do so and finally click on the OK button. A credential gets added to the Credentails folder.

That's all folks.

1 comment:

Lou Gallegos said...

Yes, new security improvements in SQL Server 2005 are just as wonderful as you could have only dreamed about. And I really love that new tools arrive on the market that use the full power of SQL security and .NET remoting. A good example of such tools would be Scriptlogic's Security Explorer for SQL. I came across this nice tool just recently and I can't wait to share my thoughts about the tool and SQL security management. You know, some of us usually think about backup as a backup of data. But just a few understand that in the world of today the term 'data' and the term 'security' go so close one to each other that you hardly can distinguish between them if you look at them as a whole. That is if you look at your SQL database as a big array of sensitive data. With SQL server you know that since everything runs well you are safe and you don't have to worry much about the information security because you know that SQL kind of guarantees you that it keeps your data protected. However, we don't count here what if SQL will fail, what if environmental conditions will force the hardware to fail, etc. Moreover counting the case that happened to my environment I'd say that the most destructive power here is the power of human beings. We are prone to making errors. And it doesn’t mean if we do them intentionally or just inadvertently. Recently we needed to perform audit on what is happening to security on our server. We decided to scan through SQL data to find our security breaches we have and fix them if we can. First off we decided to backup SQL databases to make sure we are riding the safe way. But once we counted how much space we need to backup all of them we understood that we can't afford it. I googled for a solution and quickly found that Scriptlogic's Security Explorer . That was right what we were searching for. It allowed us to backup only security permissions without needing to backup any bit of stored data! The real magic of this tool is simplicity. Everything that might be sound complicated is very easy implementable with this tool. Say, we had to backup SQL data on a remote server. All of a sudden we realized that for some reason we don't have access to that remote shared storage. That's where we put Security Explorer into the operation. The tool has a nice feature that allows it automatically override administrative privileges if you run it under administrative credentials. That was a great workaround for us to get access to a remote folder and set its Sharing ACL to distribute permissions, add access to the shared folder and etc. All that we were able to do using a single tool within the same GUI. It's like with SQL Management Studio. There are some options that I lack in SQL Studio but in general the tool is very solid made and has a nice GUI that is easy to use for a user with any level of qualification. The same thing with Security Explorer only that it's even more easy to use since it doesn’t use any specifics of working with SQL or any other object for which you want to manage security. It just allows you to treat things the way you like. That is it doesn’t hide any information about security of the object you manage. It's very helpful when you search through the security. So we just set security settings for a remote share and proceeded with security audit. First off, I got a list of users that left the company in the last few months and make a search within SQL server using Security Explorer to see what objects those users had access to. That was very easy to do with Security Explorer. Kind of reminds the way you google for something. You put a name into query field, you specify query options to optimize the search scope and get a result with the list of objects that this user has access for. Then we just backed up security for the objects we found to that remote share and continued with cleaning or security configurations from unwanted accounts. Frankly speaking that was like purging obsolete data. We got a list of items and revoked their access from the objects. That was faster than it sometimes takes for a windows search companion to find my docs in %USERPROFILE%\My Documents. At least that was so fast that I couldn't imagined. I believe that' s where properly made .NET code in Security Explorer makes the magic. Protection Status