Login Authentication for Standard Accounts

We have a couple of vendor applications that create standard accounts.
These accounts have the same format as our domain accounts. For
example, my Windows account would be <domain>\<user_name>. The SQL
standard account would be <user_name>.
What we would like to do is change the login process to validate
against active directory either prior to or instead of checking the
SQL password. Everything else could remain the same.
This would be on SQL 2000 or SQL 2005. Any suggestions or other
insight would be appreciated.RogerT (roger.tompkins@.gmail.com) writes:
> We have a couple of vendor applications that create standard accounts.
> These accounts have the same format as our domain accounts. For
> example, my Windows account would be <domain>\<user_name>. The SQL
> standard account would be <user_name>.
> What we would like to do is change the login process to validate
> against active directory either prior to or instead of checking the
> SQL password. Everything else could remain the same.
> This would be on SQL 2000 or SQL 2005. Any suggestions or other
> insight would be appreciated.
I'm not really sure what you mean here. SQL Server provides to
means of authentication: SQL authentication and Windows authentication.
It sounds from your description that the vendor accounts are for
SQL authentication. But you cannot change a login from being an SQL
login, to be a Windows login. These two types of logins are competely
unconnected.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Logical Operators in SQL statement

I want to display all valid applications based on the SQL statement below. However, it returns none even though there are 2 valid applications in the database.

newLoanSQL = "SELECT LOAN_ID, DEPENDANT_CHILDREN, RESIDENTIAL_STATUS, LOAN_AMOUNT, INTEREST_RATE, DURATION, REPAYMENTS, REPAYMENT_AMOUNT, PURPOSE, OCCUPATION, EMPLOYMENT_STATUS, EMPLOYERS_NAME, ANNUAL_INCOME, SPOUSE_NAME, GUARANTOR, SECURITY, APPROVAL_ONE, APPROVAL_TWO, APPROVAL_THREE FROM LOAN_DETAILS Where NEW_APPLICATION = '" & searchString & "' AND APPROVER1_ID <> '" & id & "' OR APPROVER2_ID <> '" & id & "' OR APPROVER3_ID <> '" & id & "'"

The SQL statement was working fine until I added the following:
AND APPROVER1_ID <> '" & id & "' OR APPROVER2_ID <> '" & id & "' OR APPROVER3_ID <> '" & id & "'

Can anyone help me please?If you mix ANDs and ORs like that you are likely to get the wrong result due to the order in which they get processed. Bracket the ORs together like this:

Where NEW_APPLICATION = ? AND (APPROVER1_ID <> ? OR APPROVER2_ID <> ? OR APPROVER3_ID <> ?)

But is that really what you want? I imagine what you meant was really:

Where NEW_APPLICATION = ? AND (APPROVER1_ID <> ? AND APPROVER2_ID <> ? AND APPROVER3_ID <> ?)

i.e. "the given ID must not appear in ANY of the 3 approver_id columns", rather than "the given ID must not appear in ALL 3 of the approver_id columns (but can appear in 1 or 2 of them)"

Note: I am encouraging you to change to bind variables at the same time. Not only do they make the code more readable and reduce typing errors, they also improve the performance and security of your application.|||I did as suggested by Tony Andrews. However, the SQL statement still is not working.

newLoanSQL = "SELECT LOAN_DETAILS.LOAN_ID, LOAN_DETAILS.DEPENDANT_CHILDREN, LOAN_DETAILS.RESIDENTIAL_STATUS, LOAN_DETAILS.LOAN_AMOUNT, LOAN_DETAILS.INTEREST_RATE, LOAN_DETAILS.DURATION, LOAN_DETAILS.REPAYMENTS, LOAN_DETAILS.REPAYMENT_AMOUNT, LOAN_DETAILS.PURPOSE, LOAN_DETAILS.OCCUPATION, LOAN_DETAILS.EMPLOYMENT_STATUS, LOAN_DETAILS.EMPLOYERS_NAME, LOAN_DETAILS.ANNUAL_INCOME, LOAN_DETAILS.SPOUSE_NAME, LOAN_DETAILS.GUARANTOR, LOAN_DETAILS.SECURITY, LOAN_DETAILS.APPROVAL_ONE, LOAN_DETAILS.APPROVAL_TWO, LOAN_DETAILS.APPROVAL_THREE, LOAN_DETAILS.NEW_APPLICATION, LOAN_DETAILS.APPROVER1_ID, LOAN_DETAILS.APPROVER2_ID, LOAN_DETAILS.APPROVER3_ID FROM LOAN_DETAILS WHERE LOAN_DETAILS.NEW_APPLICATION='Yes' AND (APPROVER1_ID <> '" & sID & "' AND APPROVER2_ID <> '" & sID & "' AND APPROVER3_ID <> '" & sID & "') OR (LOAN_DETAILS.APPROVER1_ID Is Null AND LOAN_DETAILS.APPROVER2_ID Is Null AND LOAN_DETAILS.APPROVER3_ID Is Null)"

Please, can someone help me with this as I am working to a deadline.
Thanks in advance|||NULLs are a problem in SQL too, since they are neither equal to, nor not equal to, anything - including NULL! So perhaps what you need is:

((APPROVER1_ID <> ? OR APPROVER1_ID IS NULL)
AND (APPROVER2_ID <> ? OR APPROVER2_ID IS NULL)
AND (APPROVER3_ID <> ? OR APPROVER3_ID IS NULL))|||All working now! Thanks a million, I really appreciate your help.
Cheers