Wiki Home

SQL Server Security Vs NTSecurity


Namespace: SQL
[2002.06.03] We are using DSNLess connection strings (which include user/password) to set up our RV's and SPT access from our application. We did this primarily because it provides a very flexible solution as well as it's very easy to deploy. We found out after implementation that MS states using NT Authentication vs. SQL based security is considered a "best practice" (In SQL Books Online). After looking at what would be involved in switching over to NT security, we decided it wasn't worth it (at this time). So please, don't everyone start breaking into our clients' database! {g} I'm curious, does MS often claim something is a best practice without an explanation? And, is SQL Security REALLY less vulnerable than NT security? I suppose a savvy user could hack the DBC and see the connection string. However, if the users' NT login gave them access, they wouldn't even need to run the application in order to access the data, which I would think is not very desirable either. And another thing, is cracking a SQL password any easier than cracking an NT password? -- Randy Jean

Given the same type of reasonably good password, both should be as easy to crack by brute force. In the real world, NT is better because, if properly used, it can enforce company policies where the password must be reasonably complex, must include chars and numbers, it must be renewed periodically and must not be equal to the last n passwords. This level of policy control is not available in SQL Server Authentication. Until SQL Server 2000 you could even leave the 'sa' account with no password. A stupid practice by any DBA or Netadmin but common nonetheless. -- Alex Feldstein

Very good points to consider. Will DSNLess connection string work with NT password authentication if UID and PWD are left empty in the string? This would allow us to make the switch very easily without changing the front end methodology we're using to connect. We suppress the SQL login prompt by default. BTW, not that I've ever done this {g}, but you can leave the sa password blank - the installation just HIGHLY recommends you don't. -- Randy Jean

Can you have SQL server only security? i think as of ? SQL7 you could only have NT and NT+SQL. We use horrid SQL passwords like P0L7dq1Sd4foGGf6 to prevent users guessing or hacking the password. -- John Ryan

Randy, the problem with NT security is indeed that it doesn't just give users access to data via your app, it also gives them access to data via Excel or even Word. So you need that SQL Security layer.
FWIW, the new (VFP7) CONNSTRING clause for USE means you don't need connections in your dbc any more. You can encrypt the connection string on the server and unpack into an instance property for use by your app. That way you can also Include a RV dbc in your exe- not done before because of the need for site-specific connections- and remove ODBC DSN from client PCS.
CONSTRING makes RV much more secure and even easier to deploy. -- John Ryan

Can SQL Server 2005 limit access via NT Security to only my VFP App? Maybe Microsoft can give us a reason to upgrade SQL but leave our VFP apps in place. Craig Roberts

We're not using CONNSTRING but we do a similar thing with grabbing the connection string from the server (not encryped as of yet, but a good idea), iterate through our local copy of the views DBCs at runtime and do a DBSetProp to set the connection string property. We also persist a second connection handle for SPT calls. So, each user has a max of 2 sessions in SQL when running the app. 1 for RV's (all RV's are set to share connection) and one for SPT. So far this has worked very well. I can post some sample code if anyone's interested. -- Randy Jean

Sounds like a good idea- much easier than our previous way of creating connections for each customer. Occasionally we still saw the "dbc in use" error message and we were about to alter the app to copy the views into local dbc as per Creating DBCfor Views. But connstring means we can Include a dbc with views into our exe because nothing needs to change in the View to match site-specific needs. Combined with Konxise, we think this makes our apps *extremely* secure and easy to manage. The dbc on the server is now used to contain local lookup tables only, all those views are GONE. -- John Ryan

Category Data
( Topic last updated: 2007.07.30 10:47:53 AM )