January 30, 2008 at 9:47 am
How do I use Windows authentication to allow one SQL server access to update data on another?
I'm an app developer who, in the last year, inherited responsbility for all of our SQL servers (<10). We went through an audit a couple weeks ago that determined that all of our applications and external data loads that connect to the SQL servers via SQL logins need to be updated to use Windows authentication only. I think I understand how that works for user applications, but am at a loss as to what to do with the vb6 exe's, vbscripts, and other various apps that sit on our SQL scheduling server that update data on our SQL data servers.
Would I need to use the account that SQL services runs under? Isn't that a larger security risk that local SQL accounts? What am I missing?
Laura
January 30, 2008 at 11:53 am
If the scripts are scheduled using the SQL Agent they are likely using whatever service account the Agent is running as.
If the service account is the same for all your SQL Services and Agents then you would only have to change the connection string in the applications or DSN to a Trusted connection.
For example:
From this:
Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
To this:
Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes;
If the service accounts are different then you would create a login on the target server based on a domain username or group:
CREATE LOGIN [Domain\Username] FROM WINDOWS WITH DEFAULT_DATABASE=[myDB]
One reason it's considered more secure is that you don't end up storing the password in the application or script as you may do with SQL authentication. Another is that password policies such as complexity, expiration and account lockouts on the domain are enforced.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply