June 10, 2002 at 7:36 pm
Excuse inexperience with SQL but need help. Running SQL 2000 on Windows 2000 server named SQLA. Web server running on different hardware with Windows 2000, IIS 5.0; server named WEBA.
Our programmers developed web application to be deployed to WEBA with pointers back to SQL server SQLA.
They indicate account running behind SQL needs to be a domain account in order for the application to work. What's difference of using local account vs domain account? What are server-to-server activities that might require a domain account?
Any feedback is appreciated!
June 10, 2002 at 8:18 pm
Really there is no difference except they may have created the account on the domain which they have direct access to if you block their access in production SQL. Also a domain account can have more file system permissions than SQL account and they can use authentication that people already use and with apps can synchronize easier than SQL (althought the later is some peoples opinions).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 11, 2002 at 9:49 am
I agree with Antares... You'll want to check with the developers and ask them specifically why they think the account needs to be a domain account. Is thier application using xp_cmdshell to drop out to a command shell to run operations against a remote machine? Once you know exactly what they plan on doing you can then take the appropriate action... and remember that it is always best to setup the service accounts with MINIMAL permissions.
If the box needs to access a remote machine have a new Guest Domain Account created with run as a service permissions. Grant permissions to the specific disk locations that SQL will need to access. Then grant specific permissions to the remote resource that the account will need to access.
Never setup this account as a Local Admin, it's too dangerous.
June 11, 2002 at 7:39 pm
Thanks for feedback. Will talk to developers tomorrow and see what they have to say.
June 12, 2002 at 2:04 pm
After talking to developer again, here is scenario:
We are working in single master domain model, NT 4.0. User accts are in MASTER domain and many resource domains (i.e. DomainA, DomainX)
Initially had Web/IIS and SQL on same server (ServerA in DomainA) running application. On Web server, identified default anonymous account (i.e. IUSR_ServerA). Application has pointers back to SQL database on same server. Created same username (i.e. IUSR_ServerA) within SQL and setup something called "trusted connection" on SQL side. This allowed him to access the Web interface and query the data in the SQL database; no problem.
Since then, they have split the 2. The Web/IIS is on ServerA in DomainA and SQL is now on ServerB in DomainA. He's telling me that now he can't query the data as the trusted connection is no longer there because ServerA\IUSR_ServerA is local to ServerA/Web and ServerB\IUSR_ServerA is local to ServerB/SQL. That SQL has no way to authenticate.
They propose getting away from Web/IIS default anonymous account and assign it an account from MASTER (i.e. SQLUser in MASTER). That when they go to Security/Logon/New Login within SQL, they only have options for user in MASTER, DOMAINA.
To work around the problem for now, they have hardcoded a username/password within the application.
Again, I'm new to all of this SQL stuff but I would think hardcoding username/password is NOT a good thing! From what I've read, I've gotten the sense that the anonymous acct on Web server should be sufficient enough. That SQL would be setup to use Windows authenticiaion so it would be able to read the user logged on, what permissions they have based on what's setup, etc. Is that true?
If anyone can provide feedback on what the developers are trying to do, whether we need local or domain account on Web, whether they have to create the same account on SQL, etc., I would GREATLY appreciate it!
June 13, 2002 at 10:08 am
It sounds like there are potentially two ways to handle the situation:
A) They need to setup the IUSR Account as a guest domain account, then you can create a Windows Authentication logon from the guest domain account. In this scenario they are still going to have to hardcode a database machine name into their code or save it in the registry somewhere.
B) They store the connection parameters in the registry encrypted. They setup a connection.inc that retrieves these parameters from the registry and decrypts them before using them to connect. Setup a simple little app to update the connection string in the registry.
I personally can't stand windows authentication simply because an NT Admin can easily take over an account and gain direct access to the database. Not to mention end users leaving their workstations unlocked. However is database security is not a major concern, scenario A would be much easier to implement.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply