A Quick SQL/Integrated Authentication issue (Looks like I am missing something trivial)
-
There is a reason you rarely see question about integrated security in a database forum, almost no one uses it. The normal design is for the application to have a SQL userid/password that is authorised to access the parts of the database required. I have never seen anything but a test/toy application use integrated security.
Never underestimate the power of human stupidity RAH
Dear Mycroft, This is a kind of legacy product that the folks were re-engineering. I have suggested them to use a variant of Basic Auth over SSL. A quick proto seems to be working out too. I admit your stand that 'Integrated Security' is a horrendous flop-show staged by SQL Server.
Vasudevan Deepak Kumar Personal Homepage
Tech Gossips
The woods are lovely, dark and deep, But I have promises to keep, And miles to go before I sleep, And miles to go before I sleep! -
A simple code as below:
void Page\_Load(object sender, EventArgs e) { try { SqlConnection objConnection = new SqlConnection("Data Source=MySQLServer;Initial Catalog=gqs;Integrated Security=True;"); objConnection.Open(); } catch (SqlException objException) { Response.Write(objException.Message); } Response.Write("<br><Br>The page is executing as " +Thread.CurrentPrincipal.Identity.Name); }gives the following output: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. The page is executing as MyDepartmentDomain\deepak When the thread is running as me how does SSPI pickup Anonymous Logon?
Vasudevan Deepak Kumar Personal Homepage
Tech Gossips
The woods are lovely, dark and deep, But I have promises to keep, And miles to go before I sleep, And miles to go before I sleep! -
There is a reason you rarely see question about integrated security in a database forum, almost no one uses it. The normal design is for the application to have a SQL userid/password that is authorised to access the parts of the database required. I have never seen anything but a test/toy application use integrated security.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
almost no one uses it. The normal design is for the application to have a SQL userid/password that is authorised to access the parts of the database required
:wtf: You think SQL authentication is preferable to Windows Authentication in a production environment? Or did I misunderstand?
-
Mycroft Holmes wrote:
almost no one uses it. The normal design is for the application to have a SQL userid/password that is authorised to access the parts of the database required
:wtf: You think SQL authentication is preferable to Windows Authentication in a production environment? Or did I misunderstand?
Nope you did not misunderstand, in over 20 years of database development I have only twice seen windows authentication used and they were both small organisations.
Never underestimate the power of human stupidity RAH
-
Nope you did not misunderstand, in over 20 years of database development I have only twice seen windows authentication used and they were both small organisations.
Never underestimate the power of human stupidity RAH
Ok, I have an even smaller sample size than you, but in 15 years of SQL/web development i have only once seen SQL authenttication used. The wisdom passed to be by my elders was that windows authentication could be kept significantly more secure, primarily by centralising policies across (potentially) multiple instance of sql server. Almost everywhere ive ever worked has specifically not installed sql with mixed mode security, making the use of sql authentication actually impossible. Edit: this article seems to backup my claims above: http://databases.about.com/od/sqlserver/a/authentication.htm[^]
"Microsoft’s best practice recommendation is that you use Windows authentication mode whenever possible. The main benefit is that the use of this mode allows you to centralize account administration for your entire enterprise in a single place: Active Directory. This dramatically reduces the chances of error or oversight.
For example, consider the scenario where a trusted database administrator leaves your organization on unfriendly terms. If you use Windows authentication mode, revoking that user’s access takes place automatically when you disable or remove the DBA’s Active Directory account. If you use mixed authentication mode, you not only need to disable the DBA’s Windows account, but you also need to comb through the local user listings on each database server to ensure that no local accounts exist where the DBA may know the password. That’s a lot of work! "
Edit2: The general wisdom seems to be that if you support multiple platforms connecting to SQL, then SQL Authentication is your only option. If all clients are on a windows domain, use windows auth. (source: http://blogs.msdn.com/b/jjameson/archive/2007/03/23/sql-server-authentication-modes.aspx[^]). Its certainly got nothing to do with company size or programmer experience!
-
Dear Pete, The Virtual Directory has 'Anonymous Authentication' disabled. It is a Windows Server 2008 R2. The virtual directory has only Windows Authentication enabled.
Vasudevan Deepak Kumar Personal Homepage
Tech Gossips
The woods are lovely, dark and deep, But I have promises to keep, And miles to go before I sleep, And miles to go before I sleep! -
Ok, I have an even smaller sample size than you, but in 15 years of SQL/web development i have only once seen SQL authenttication used. The wisdom passed to be by my elders was that windows authentication could be kept significantly more secure, primarily by centralising policies across (potentially) multiple instance of sql server. Almost everywhere ive ever worked has specifically not installed sql with mixed mode security, making the use of sql authentication actually impossible. Edit: this article seems to backup my claims above: http://databases.about.com/od/sqlserver/a/authentication.htm[^]
"Microsoft’s best practice recommendation is that you use Windows authentication mode whenever possible. The main benefit is that the use of this mode allows you to centralize account administration for your entire enterprise in a single place: Active Directory. This dramatically reduces the chances of error or oversight.
For example, consider the scenario where a trusted database administrator leaves your organization on unfriendly terms. If you use Windows authentication mode, revoking that user’s access takes place automatically when you disable or remove the DBA’s Active Directory account. If you use mixed authentication mode, you not only need to disable the DBA’s Windows account, but you also need to comb through the local user listings on each database server to ensure that no local accounts exist where the DBA may know the password. That’s a lot of work! "
Edit2: The general wisdom seems to be that if you support multiple platforms connecting to SQL, then SQL Authentication is your only option. If all clients are on a windows domain, use windows auth. (source: http://blogs.msdn.com/b/jjameson/archive/2007/03/23/sql-server-authentication-modes.aspx[^]). Its certainly got nothing to do with company size or programmer experience!
Your arguments are perfectly valid right up until they meet the inertia of outsourced support where it can take 2 weeks to get a new user group creaded in AD. You need 15 pages of forms and 2 interviews to justify the group. I need to respond to user requirements in hour (or quicker) not in days/weeks. And yeah I seen a multipage exit document signoff.
Never underestimate the power of human stupidity RAH
-
Your arguments are perfectly valid right up until they meet the inertia of outsourced support where it can take 2 weeks to get a new user group creaded in AD. You need 15 pages of forms and 2 interviews to justify the group. I need to respond to user requirements in hour (or quicker) not in days/weeks. And yeah I seen a multipage exit document signoff.
Never underestimate the power of human stupidity RAH
-
Yes. I did went through all those procedures.
Vasudevan Deepak Kumar Personal Homepage
Tech Gossips
The woods are lovely, dark and deep, But I have promises to keep, And miles to go before I sleep, And miles to go before I sleep! -
So its basically your very specific situation which precludes you using the prefered method of security. This is totally different from what you originally said:
almost no one uses itandtest/toy application use integrated securityNope I tend to work for large organisations that have a huge amount of inertia. While integrated may be the
preferredmethod I have never seen it implemented in a large organisatrion.J4amieC wrote:
very specific situation
I just finished arguing with IT so my example may have been a little narrow :(
Never underestimate the power of human stupidity RAH
-
There is a reason you rarely see question about integrated security in a database forum, almost no one uses it. The normal design is for the application to have a SQL userid/password that is authorised to access the parts of the database required. I have never seen anything but a test/toy application use integrated security.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
There is a reason you rarely see question about integrated security in a database forum, almost no one uses it
In that case I've been a nobody for over a decade. I don't like adding passwords to authenticate a user that's already logged in.
Mycroft Holmes wrote:
I have never seen anything but a test/toy application use integrated security.
"Therefore, it doesn't exist?" :D
Bastard Programmer from Hell :suss:
-
Mycroft Holmes wrote:
There is a reason you rarely see question about integrated security in a database forum, almost no one uses it
In that case I've been a nobody for over a decade. I don't like adding passwords to authenticate a user that's already logged in.
Mycroft Holmes wrote:
I have never seen anything but a test/toy application use integrated security.
"Therefore, it doesn't exist?" :D
Bastard Programmer from Hell :suss:
Eddy Vluggen wrote:
I don't like adding passwords to authenticate a user that's already logged in.
Why on earth would you need to do that. I have the user log in using active directories for authentication, then use those details to get the application specific authorisation. Meanwhile the application logs onto the database using a SQL userid/password (actually the WCF service logs onto the databse).
Eddy Vluggen wrote:
Therefore, it doesn't exist
Nah probably just not commonly used! Actually that might be an interesting survey.
Never underestimate the power of human stupidity RAH
-
Eddy Vluggen wrote:
I don't like adding passwords to authenticate a user that's already logged in.
Why on earth would you need to do that. I have the user log in using active directories for authentication, then use those details to get the application specific authorisation. Meanwhile the application logs onto the database using a SQL userid/password (actually the WCF service logs onto the databse).
Eddy Vluggen wrote:
Therefore, it doesn't exist
Nah probably just not commonly used! Actually that might be an interesting survey.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
Why on earth would you need to do that
Your users need access to an additional secret (the sql username/password). I'd say they're already logged in to the system. Where do you keep the secret? Your app needs access to it, so it's entered by the user or it's stored somewhere.
Mycroft Holmes wrote:
Nah probably just not commonly used!
That doesn't make it a bad idea :)
Mycroft Holmes wrote:
Actually that might be an interesting survey.
True :thumbsup:
Bastard Programmer from Hell :suss:
-
Mycroft Holmes wrote:
Why on earth would you need to do that
Your users need access to an additional secret (the sql username/password). I'd say they're already logged in to the system. Where do you keep the secret? Your app needs access to it, so it's entered by the user or it's stored somewhere.
Mycroft Holmes wrote:
Nah probably just not commonly used!
That doesn't make it a bad idea :)
Mycroft Holmes wrote:
Actually that might be an interesting survey.
True :thumbsup:
Bastard Programmer from Hell :suss:
Eddy Vluggen wrote:
Your users need access to an additional secret
Of course not the Application has the credentials, either an encrypted string in the config file or hard coded inside the app. Users have already been authenticated when they log into the app using AD.
Never underestimate the power of human stupidity RAH