How do you make an ODBC connection not use the "Use Trusted Connection"?

[forest demon]

I have an Access front end that uses an ODBC connection to a SQL
Server database.

Every time i come into my Access front end and try to execute a query,
it fails and the prompts me with a SQL Server Login .

The dialog has the server name, a checkbox for "Use Trusted
Connection", a login ID and password. of course if the checkbox is
checked, the login ID and password are greyed out. It defaults to the
checkbox being checked.

I can't for the life of me find out where to change it so the checkbox
is always unchecked by default and it uses a specific login ID and
password each time.

any help is appreciated.

thanks...

-fd

[Norman Yuan]

Obviously, the ODBC Data Source is created with Windows security (Trusted
Connection). That means user's (youor) user account is used in the Access
application to connect to SQL Server. If your windows account does not have
required permission to access said SQL Server database, then you get the
prompt.

If you cannot change the DSN, then you need to have your windows account
given needed access to the SQL Server database; or you can change the DSN to
use SQL Server security and enter SQL Server login username/password there,
so that any application using that DSN will not need username/password to
log into SQL Server database (of course you need to make sure data is
correctly secured).

Post by forest demon
I have an Access front end that uses an ODBC connection to a SQL
Server database.
Every time i come into my Access front end and try to execute a query,
it fails and the prompts me with a SQL Server Login .
The dialog has the server name, a checkbox for "Use Trusted
Connection", a login ID and password. of course if the checkbox is
checked, the login ID and password are greyed out. It defaults to the
checkbox being checked.
I can't for the life of me find out where to change it so the checkbox
is always unchecked by default and it uses a specific login ID and
password each time.
any help is appreciated.
thanks...
-fd

[forest demon]

Post by Norman Yuan
Obviously, the ODBC Data Source is created with Windows security (Trusted
Connection). That means user's (youor) user account is used in the Access
application to connect to SQL Server. If your windows account does not have
required permission to access said SQL Server database, then you get the
prompt.
If you cannot change the DSN, then you need to have your windows account
given needed access to the SQL Server database; or you can change the DSN to
use SQL Server security and enter SQL Server login username/password there,
so that any application using that DSN will not need username/password to
log into SQL Server database (of course you need to make sure data is
correctly secured).

Post by forest demon
I have an Access front end that uses an ODBC connection to a SQL
Server database.
Every time i come into my Access front end and try to execute a query,
it fails and the prompts me with a SQL Server Login .
The dialog has the server name, a checkbox for "Use Trusted
Connection", a login ID and password. of course if the checkbox is
checked, the login ID and password are greyed out. It defaults to the
checkbox being checked.
I can't for the life of me find out where to change it so the checkbox
is always unchecked by default and it uses a specific login ID and
password each time.
any help is appreciated.
thanks...
-fd- Hide quoted text -

- Show quoted text -

i found a workaround; however,
changing the DSN to use SQL Server security by entering SQL Server
login username/password
does not work. it still errors out and then shows the prompt with the
"Use Trusted Connection"
box checked. it's like it doesn't save the DSN config.

thanks for your input...

-fd

[W***@gmail.com]

Post by forest demon

Post by Norman Yuan
Obviously, the ODBC Data Source is created with Windows security (Trusted
Connection). That means user's (youor) user account is used in the Access
application to connect to SQL Server. If your windows account does not have
required permission to access said SQL Server database, then you get the
prompt.
If you cannot change the DSN, then you need to have your windows account
given needed access to the SQL Server database; or you can change the DSN to
use SQL Server security and enter SQL Server login username/password there,
so that any application using that DSN will not need username/password to
log into SQL Server database (of course you need to make sure data is
correctly secured).

Post by forest demon
I have an Access front end that uses an ODBC connection to a SQL
Server database.
Every time i come into my Access front end and try to execute a query,
it fails and the prompts me with a SQL Server Login .
The dialog has the server name, a checkbox for "Use Trusted
Connection", a login ID and password. of course if the checkbox is
checked, the login ID and password are greyed out. It defaults to the
checkbox being checked.
I can't for the life of me find out where to change it so the checkbox
is always unchecked by default and it uses a specific login ID and
password each time.
any help is appreciated.
thanks...
-fd- Hide quoted text -

- Show quoted text -

i found a workaround; however,
changing the DSN to use SQL Server security by entering SQL Server
login username/password
does not work.  it still errors out and then shows the prompt with the
"Use Trusted Connection"
box checked. it's like it doesn't save the DSN config.
thanks for your input...
-fd- Hide quoted text -
- Show quoted text -

...would you mind sharing your workaround?

I have code to dynamically re-create and re-link the SQL tables,
however performing those operations continually increases the .mdb
file size. Access is used only as a code container, so I prefer to
keep the file size compact rather than have it grow on a daily basis.

[Rick Brandt]

"forest demon" <***@gmail.com> wrote in message news:866bb820-8a39-4f18-bbc6-***@s37g2000prg.googlegroups.com...
i found a workaround; however,
changing the DSN to use SQL Server security by entering SQL Server
login username/password
does not work. it still errors out and then shows the prompt with the
"Use Trusted Connection"
box checked. it's like it doesn't save the DSN config.

If you look carefully at the dialog when you are building a DSN you will see
that the option to supply a user name and password instead of using a trusted
connection is for temporarily connecting to the server to retrieve default
values for some of the properties of the DSN. It is NOT saving your user name
and password within the created DSN and is not intended to do so. When you
don't use trusted connections you are expected to supply your credentials at
connect time.

[She Thru Whom All Data Flows]

So, my dsn looks like this:
[ODBC]
DRIVER=SQL Server
UID=MyID
PWD=MyPWD
DATABASE=myDB
WSID=DTMEVTPC1648
APP=Microsoft Office XP
SERVER=dtmevtmsql01

Are you saying that even though I've got the userID and PWD in the dsn, the
SQL server login is still going to ignore it?

Post by forest demon
i found a workaround; however,
changing the DSN to use SQL Server security by entering SQL Server
login username/password
does not work. it still errors out and then shows the prompt with the
"Use Trusted Connection"
box checked. it's like it doesn't save the DSN config.
If you look carefully at the dialog when you are building a DSN you will see
that the option to supply a user name and password instead of using a trusted
connection is for temporarily connecting to the server to retrieve default
values for some of the properties of the DSN. It is NOT saving your user name
and password within the created DSN and is not intended to do so. When you
don't use trusted connections you are expected to supply your credentials at
connect time.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[unknown]

Hi Rick,

I do have the same problem of end users getting prompted for SQL server authentication.

May I request you for the work around?

THanks

Mahidhar

[John Bell]

Post by unknown
Hi Rick,
I do have the same problem of end users getting prompted for SQL server authentication.
May I request you for the work around?
THanks
Mahidhar

Hi Mahidhar

To use SQL Server Authentication, you will need to make sure that your
instance is configured to accept SQL Server and Windows Authentication. For
SQL 2000 the connection string is something like:

Driver={SQL
Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

For SQL Server 2005 you can use the Native driver:

Driver={SQL Native
Client};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

John

[l***@gmail.com]

I too am faced with this problem in Access 2010. Though configured to login with with a SQL Server Login I continue to get an error at connect time saying the windows authentication failed and get the SQL Server login with the checkbox for "Use Trusted Connection" checked and the login ID and password are greyed out. Has anyone figured out how to tell Access to us the login ID and password first instead of trying and failing at window authentication?