SQL Users Windows Authentication

[Troy Berardo]

We've had an Access 2000 front-end with a SQL 2000 back-end application
running for a couple of years with no problems. Users access the app through
Terminal Server using a run-time version of Access.

In the interest of having users change their own passwords regularly, we'd
like to change all SQL users from SQL Authentication to Windows
Authentication so when they login to Terminal Server the app uses that name
and password.

We know how to change the user in SQL to use Windows Authentication, so
that's not a problem. What we'd like to find is a sample piece of code
showing how to authenticate the user using Trusted Connection = Yes in the
connection string, if that's the proper way to do it. Basically, when they
open the database, before anything else is done, can we ensure that they are
a user defined within our SQL database?

Thanks for your help!

Troy
---

 

[Rick Brandt]

We've had an Access 2000 front-end with a SQL 2000 back-end application
running for a couple of years with no problems. Users access the app through
Terminal Server using a run-time version of Access.

In the interest of having users change their own passwords regularly, we'd
like to change all SQL users from SQL Authentication to Windows
Authentication so when they login to Terminal Server the app uses that name
and password.

We know how to change the user in SQL to use Windows Authentication, so
that's not a problem. What we'd like to find is a sample piece of code
showing how to authenticate the user using Trusted Connection = Yes in the
connection string, if that's the proper way to do it. Basically, when they
open the database, before anything else is done, can we ensure that they are
a user defined within our SQL database?

That's pretty much how "Trusted Connections" work. The NT username and
password is required to get on the domain and if you're on the domain the
SQL Server knows who you are. You shouldn't have to do anything other than
change your ODBC settings on each client to use "Trusted Connections".

 

[Troy Berardo]

Right, I understand that SQL Server knows who I am, but how does the
application know if the user who is logged in to the domain is allowed to
use the application?

Example:
Troy logs in properly to the Domain.
In SQL Server, Troy is defined as a Windows Authentication User.
Troy opens an Access application with a SQL back-end.
Should the application allow Troy access?

I don't want to assume that if someone logs into the domain that they can
use the application. If they aren't a SQL user, I don't want the app to
crash; I want to check to see if the user has access to the system and
notify them if they don't.

I hope this is clear.

Troy

 

[Rick Brandt]

Right, I understand that SQL Server knows who I am, but how does the
application know if the user who is logged in to the domain is allowed to
use the application?

Example:
Troy logs in properly to the Domain.
In SQL Server, Troy is defined as a Windows Authentication User.
Troy opens an Access application with a SQL back-end.
Should the application allow Troy access?

I don't want to assume that if someone logs into the domain that they can
use the application. If they aren't a SQL user, I don't want the app to
crash; I want to check to see if the user has access to the system and
notify them if they don't.

Well Trusted Connections just means the server "knows" who you are. It doesn't
mean you have permissions to anything. On the server you still have to set up
groups and assign users to them and assign specific permissions to those groups
and/or individual user accounts.

If Troy is "recognized" but doesn't explicitly have permissions to the
"Accounts" database then he will not be able to access anything in that
database. If your Access app tries to access anything in that database while
Troy is logged in he will get an ODBC error.