SQL Server Integrated Security without a Domain

Question

This question potentially sits somewhere between ServerFault and DBA Admins but the Domain element of this as led me to put it here.

We have been developing an SOA product and are thinking about different deployment scenarios for our managed servers at client's office. The trigger for this question was discussion about using SQL Server FILESTREAM which requires integrated security

SQL logins will not work with FILESTREAM containers. Only NTFS authentication will work with FILESTREAM containers. FILESTREAM MSDN

At the moment FILESTREAM is the only reason we have for using Integrated Security which makes the potential requirement to deploy Domain Controllers (and their redundancy requirements) to the manager servers unappealing.

I've had a look around and there's a few questions suggesting that you can use WORKGROUPS with Integrated Security.

Integrated Security (SSID?) in a no-domain...

My question is, is there a recommended practice for the scenario described above? Should we give WORKGROUP security a go and see how we get on or is there a reason that's a big no no and we should either use a domain or not use FILESTREAM?

Answer 1

In general, Integrated Security (aka Trusted Connection) is more secure than SQL logins and is preferred. The reason is that the services that need to connect to SQL Server don't need to have username and passwords hard-coded anywhere; they can just run with a user that has permissions to access SQL Server.

To use Integrated Security, a domain is not required. If you are managing many machines, than a domain would be preferred, but with a small number of servers you can just use local users on each server. The trick is that the shared users all need to have the same username and password on each server that is accessing SQL data. For example, suppose you have an IIS web server on one machine and a SQL Server on another. You would create a user with the same username and password on both the web and SQL Server machines, perhaps called "IISUser". Then in SQL Server you would assign the appropriate permissions to that user, and on the web server you would set the application pool of IIS to run with that same user. Because this is using Integrated Security, no username/password needs to be written or stored in any config file, and the connection is secure.

Unfortunately I cannot answer your question of "is there a recommended practice" regarding FILESTREAM, however I would offer that you shouldn't avoid doing what you want to do simply because you also want to avoid Integrated Security. IMO you should be using Integrated Security regardless. I would suggest that you do give local users a try for your situation. Ultimately a domain will be better than local users (it will be even more secure and easier to manage), but I would consider Integrated Security with local users still better than SQL Server logins.