0

I have a service on server A that needs to connect to a SQL Server instance (2005 or 2008) on server B. Both A and B are on the same domain. What are the best practices for configuring both the service and SQL Server so that the former can connect to the latter?

In particular:

  • I want to use Windows Authentication, right? Not store passwords in the registry or something.
  • Should the service be running as NT AUTHORITY\NetworkService, or some other account?
  • What logins need to be created on the SQL Server?
John
  • 105
  • 2

1 Answers1

3

The short answer: It depends.

  • Windows Authentication is considered to be a best practice, but I have a number of situations in my environment where it simply isn't practical for various reasons.
  • I wouldn't run either the service or SQL Server with NetworkService - it has too many unnecessary permissions. Since you're on a domain, create domain accounts with privileges limited to only what you need to run each service.
  • The minimum set of logins would include any sysadmins you need (SQL2008 doesn't create any logins for you) and the service account for your service on server A. Your environment may also require logins for other things, such as monitoring tools or users who need to connect directly to the database.
  • In keeping with the principle of least privilege, each login should be limited to only those permissions on the server that are required to perform their functions.
Ed Leighton-Dick
  • 1,094
  • 1
  • 7
  • 12
  • +1, all sensible stuff. – Maximus Minimus Oct 21 '09 at 21:07
  • When you say "create a SQL server login for the service account", do you mean create a login for the limited-privilege domain account that the server is running as? Or is there a way add a login for the service directly, regardless of what user it runs as? – John Oct 21 '09 at 23:54
  • Yes, a login for whatever domain account your service on server A runs as. You won't be able to map the login to the service. On SQL2008 running on Win2008, you can map a login to a service, but I believe it only works for services on the same computer as SQL. – Ed Leighton-Dick Oct 27 '09 at 19:14