June 27, 2002 at 1:12 pm
I enjoyed the article.
Charlene
June 27, 2002 at 1:31 pm
Interesting article
June 27, 2002 at 2:36 pm
Windows Auth is not an option for the internet/application server prov model. We will never use IIS and therefore not use it's inbuilt accounts. Thanks for the article!
June 30, 2002 at 11:55 am
I was glad to read Andy's argument -- it makes me feel better about our method. We use both WA and SQL logins: WA authorizes a person to get on our network and run one of our VB apps. The VB app identifies the user, looks up the username in a table maintained by the developers to determine the user's access rights to the database, then performs the login to the SQL server using the appropriate SQL Server login (which will differ based on the databases and the level of access for that app and user). This way, our VB apps have total control over data entry, and we can allow full read-only access (for domain users, of course) via MS Access, Excel, etc., without worrying about the users changing data or structures with these tools.
Now I'm just waiting for Andy's details on providing greater security to the SQL logins. I'm particularly interested in how to encrypt the VB code and .EXE files that contain the SQL login info, and possible additional security methods for protecting the SQL Server logins themselves.
Patrick Stair
Edited by - stair on 06/30/2002 12:03:00 PM
June 30, 2002 at 10:21 pm
Yes, interesting article for sure, however I disagree with the broad sweeping statement that WA is "bad"...I would say that, like almost everything else that is configured by DBA Admins/Developers in SQL Server, it is surely comes down to the needs of the specific application itself.
We are running a fairly large multi-tier internet based application (Web server(s) to Middle tier(s) to DB), completely with WA. All it takes is a little NT (group and user) to MTX to SQL (group and user) security configurations and all operates quite well (and securely) end to end, no frigging around with SQL Login management at all. Our security model is not "ideal" due to some software restrictions however it suits the applications needs perfectly...
In fact considering all the applications I have worked with, I cannot ever think of anytime I have ever found SQL Logins a better approach to WA...although that is probably reflective of the applications I've worked on... 🙂
Again, like most stuff in SQL, it becomes a trade off...and in this case its probably one of security/management vs useability/practicality (and vs development time too?)
July 2, 2002 at 2:03 pm
In terms of being provoking (which is helpful to get someone to read it), this is well written. But I disagree with the main point that SQL logins are usually best.
We use an n-tier architecture in which server-side applications are responsible for determining what authentication is important to them. The apps themselves use a Windows account whose password is known only to a group of network administrators. (We then assign roles to these program IDs for granting database permissions.) Developers and DBAs are both out of the business of knowing or caring about password administration. By necessity we also use SQL accounts for apps that can't do WA. I've audited our database access...guess which account type has been abused, and which one hasn't? In my book, both for security and for lower administrative hassle, I recommend WA.
July 9, 2002 at 9:28 am
SQL logins are the only way to handle connections for web applications that connect directly from ASP code to the database with no intermediate components; otherwise, SQL logins have no purpose. SQL logins are still used for a multitude of web-based intranet applications but new ones will be using account delegation allowing us to use the same security model as we do for client-server apps.
I manage several SQL Servers with dozens of small application databases. Windows Authentication allows me to grant permission to a Domain Group and allow the application administrators to manage the users in the groups so I don't have to manage hundreds of SQL Server logins. It is not uncommon for a single database to have several groups with access. One group might have access to the usp_Employee_SetSalary stored procedure while the rest would not; thus, negating the problem with someone accessing the database through a means other than the intended application.
Connection information should not be compiled into an application (a deployment nightmare when anything changes in a large application) and passwords should certainly not be left lying around in the registry or files. MTS provides a security model for components that will allow them to connect to SQL Server using a domain account supplied during setup of the component package on the application server. The password for this domain account would only be known by the system administrators and, especially, not by developers.
This was definitely a good article as it really made me think about the security issues but I think that each point has a subtle flaw, except with the case for using SQL logins for simple web apps.
Design security as a basic part of the database and application design, not as an afterthought for the DBA to handle when the app goes production.
Bryant E. Byrd, MCDBA
SQL Server DBA/Systems Engineer
Intellithought, Inc.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
July 11, 2002 at 3:52 am
We use WA for an Internet solution where no-one is automatically on the domain. This forces users to login to the internet application.
It is the Windows Group that is given access, not the individual, however we interogate the group using xp_logininfo to match the actual user and group to decide priviledges.
I've not used Active Directory so I can't comment on whether this helps/hinders operations.
The point you raise of a user connecting to the SQL database and running sp_setsalarylevel I feel is invalid because this means that the user has the rights to run the proc anyway through the legitemate approach so why mess about with Access et al to run it illegitemately. Its a bit like stealing your own car!
If the user shouldn't be able to run sp_setsalarylevel then surely the design of your security is flawed in any case?
July 11, 2002 at 6:08 am
I have to say that I am surprised that you have so many eople agreeing with you.
You have stated that you want to use a COM+ application, and then assumed that this means you should not use integrated security?
Why do you assume this?
And why do you assume that this means integrated security won't work for int*net applications?
If you perform you access checking using roles within the COM+ application, but run the COM+ application under a specific NT account you can keep the inherently better security without any of your tated problems.
July 11, 2002 at 7:04 am
In our main application, almost all use is by internet users calling our database from a vertical market application. Each one has a login (SQL of course) and it's no big deal to administer them, we have add user and delete user SP's to do it in one easy step.
If we didn't have the individual logins, it would be much harder to keep usage logs for tracking and troubleshooting.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
July 11, 2002 at 7:16 am
This is a hot item and I would like to see more articles like it. As a dba I don't understand all the security from the developer's side.
July 11, 2002 at 7:36 am
Windows Authentication can be used in an internet application if AD is running and the client is W2K. Delegation of authentication is a feature of Kerberos to work in a multi-tier environment. We try to use WA when we can, we have multiple databases and from what I've been told, licensing on a per seat basis is less. One user could be using multple sql logins each one counting as a seat.
July 11, 2002 at 9:13 am
A good topic, but too short! There are more reasons to use SQL logins such as support of 3rd party apps and functioning in a LAN with seperate IIS and SQL servers with no AD. That is two additional reasons. How about a poll asking how many servers you support and how many are wa only, mixed, or whatever?
July 11, 2002 at 9:21 am
Good suggestion on the poll. We'll queue it.
Andy
July 11, 2002 at 9:32 am
One more consideration is connection pooling. SQL Logins via IIS are pooled better than WA. Therefore, resources like memory are better managed.
Viewing 15 posts - 46 through 60 (of 75 total)
You must be logged in to reply to this topic. Login to reply