SQL Server Integrated Security
I've been looking hard to sort out security issues on SQL Server. We are developing a .NET application targeting SQL Server 2008 and we want to use FileStream.
Now I found out that SQL Server allows FileStream through Win32 API if you are using Integrated Security. The problem is that we have about 80% of our application complete, but it is completely based on SQL authentication. This way we do INSERTs directly from our application and don't use stored procedures for every CRUD operation.
This is relatively secure because I can store the SQL username and password encrypted. I know the password is carried over to Clear Text, but I'm willing to accept that.
We want end users to be able to connect to the database using tools like Crystal Reports, and for that we have an additional SQL login that has only SELECT rights.
Now if we go to integrated security, we need to grant individual users (via AD groups, etc.) the right to do what the application can do. Otherwise, the application will not be able to do it. But then the end user will also have these rights when he connects directly to the database.
I see people saying that you should use stored procedures for every CRUD operation and only grant EXEC rights to the AD group, but how do I do that? I don't see how the user will have different permissions when connecting directly or via an app ... Can someone enlighten me on this.
Additional question for bonus points: Intergrated Security will not work on a working group as far as I understand. How do people get a FileStream to work in a workgroup? Or is it considered impossible?
source to share
-
Integrated security will work in a workgroup using a legacy mechanism where you have the same username and password on two machines. In addition, a domain user can use the legacy mechanism to log on to a server without a domain if the server has an appropriate user account.
-
Integrated security can even handle inappropriate usernames and passwords. This might help you in your scenario.
Try the following:
NET USE \\DBSERVER /USER:DOMAIN\USERNAME
You will be prompted for a password. This establishes a NetBIOS session with the database server. After you do this, you should be able to see the shared folders and shared printers on the database server.
Once a netbios session is established between the client computer and the database server, you will be able to use the built-in security without prompting for a password.
You can specify "named pipes" as the network protocol for usem if it doesn't work with TCP (but I think it will). Named pipes inherit your existing NetBIOS session, so assuming you can list the shares you probably like.
You can also establish a login session using the Windows API function NetUseAdd
with information USE_INFO_2
(level 2), which includes the password.
My guess is the short answer is that you can have a special Windows login for your application and users can login using that. However, keep in mind that they also cannot connect to the same server using their own username and password.
source to share