SQL Server Integrated Security

Question

I've been searching hard to get my head around security related issues in a SQL Server. We're developing a .NET application that targets SQL Server 2008 and we want to use FileStream.

Now I've found out that SQL Server only allows FileStream through the Win32 API if you use Integrated Security. The problem is that we have around 80% of our application finished, but it is entirely based on SQL Authentication. So we are doing INSERT's straight form our application and are not using Stored Procedures for every CRUD operation.

This is relatively safe because I can store the the SQL username and password in an encrypted form. I know the password is transported in Clear Text, but I'm willing to accept that.

We want end-users to be able to connect to the databse through tools such as Crystal Reports and for that we have an extra SQL login that has only SELECT-rights granted.

Now, if we change to Integrated Security we would have to give individual users (via AD groups, etc.) rights to do the things the application can do. Otherwise the application would not be able to do it's work. But then the end-user would also have these rights when he connects straight to the DB.

I see people saying that you should use Stored Procedures for every CRUD operation and grant the EXEC-rights only to the AD-group, but how would I do this? I do not see how a user would have different authorizations when he connects directly or through the application... Can anybody enlighten me on this.

An extra question for bonus-points: Intergrated Security will not work on a Workgroup as far as I understand. How do people get FileStream to work in a Workgroup then? Or is this considered an impossibility?

Answer 1

1. Integrated security WILL work in a workgroup, using the legacy mechanism, where you have a matching username and password on the two machines. Also, a domain user can use the legacy mechanism to log into a non-domain server if the server has a matching user account.

2. Integrated security can even work with non-matching usernames and passwords. This may help you in your scenario.

Try this:

NET USE \\DBSERVER /USER:DOMAIN\USERNAME 

You will be prompted for your password. This establishes a NetBIOS session with the database server. You should be able to see the shared folders and shared printers on the database server once you have done that.

Once a netbios session has been established between the client computer and the database server, you will THEN be able to use integrated security without being prompted for a password.

You may have to specify "named pipes" as the network protocol to usem, if it doesn't work with TCP (but I think it will). Named Pipes inherits your existing NetBIOS session, so provided you can list the shares you are probably good to go.

You can also establish the logon session using the windows API function NetUseAdd with USE_INFO_2 (level 2) information which incorporates the password.

I guess the short answer then is that you can have a special Windows logon for your application and have the users log in using that. However note that they cannot also be connected to the same server using their own username and password.