August 18, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/thecaseforsqlloginsparttwo.asp>http://www.sqlservercentral.com/columnists/awarren/thecaseforsqlloginsparttwo.asp
August 19, 2002 at 8:43 am
Thank you for the summary. One note: we were not able to use SQL logins when there was a one-way trust relationship between the domain the server was in (trusted) and the domain the application server was in(untrsuted). In this case we used WA and had to duplicate the username/password in both domains.
August 19, 2002 at 10:54 am
I still think a good reason to have WA is that it forces the person to have authenticated to our domain. Using SQL Logins someone could (in theory) execute lots of commands or even use SQL Enterprise Manager and never authenticate to the domain. Not the Win2k or NT domain security is the best in the world either. In my mind it would be more difficult to first join the domain (I could be wrong, wouldn't be the first time).
Ross
August 19, 2002 at 3:40 pm
Fair point Bozo. Assuming some sort of diligence forcing someone to log in to the network does present a degree of security. If you use the keymaster concept, no one really has the sql login though, so it's really down to someone hacking the login/password, or the keymaster table! Long passwords are still tough to crack - see the review Steve Jones recently posted, and require access to the sysxlogins table to get the value to test against anyway.
Bill, trusts are interesting. Afraid I know more theory than practice on that one, never really had to develop in that environment. Duplicating username/pwd truly horrible, what a pain to have to keep up with that!
Andy
August 19, 2002 at 4:25 pm
It is an interesting article but one aspect that might be more explored is the use of Access 2000/XP as a front end to SQL Server. Access does not encrypt the password when you save it, so that you have a single logon for the application and then you build your own security users / roles set of tables. You then write your stored procedures to check these tables for authentication on each request, so that all access to the system is through Access. But there is a gaping hole in the security because of the way an Access project stores the original connection value.
Maybe there should be a follow up article on Access projects as a UI for SQL and how to tie it down considering the problem I have just pointed out. Writing a dedicated app is not an option because the home of this app is a govt agency and there is no way they want to deploy 10 or more so exes to the workers. Access project offers a simple way (web is out for similar reasons) to have a powerful rich client and no headaches for deployment.
October 1, 2002 at 9:13 am
You make good points and I apologize for my late response.
One point you miss is that you can easily do dynamic searches via stored procs by selecting the data set from the tables or views involved into a temp table within the stored proc, and then executing your dynamic search against the temp table. This eliminates the permissions problem normally presented when using dynamic SQL within a stored proc.
October 1, 2002 at 8:43 pm
Interesting idea. I'll have to think about that for a while!
Andy
October 2, 2002 at 9:36 am
Wouldn't you still need permissions to load the temp table?
Steve Jones
December 3, 2002 at 10:02 am
I tend to agree with this article!
This is also a good approach for databases that were not designed "correctly" and have left the gates wide open. It is far easier and cost effective (after the fact) to provide application level security through one login/encrypted password per app (and thereby lock down all users) than it would be to rewrite everything to use stps and views.
I also tend to think it is faster to develop in the first place using dynamic SQL. However, if a company were to pay to have a full time db admin around to setup and manage everything, the other way would not be too bad.
Thanks for the article.
-Rob Hajicek
SevenStar Technologies
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply