Getting Access, SQL Server, and Excel to play nice w/ ODBC logins

[LoriK]

I have access to a SQL server database via a gui w/ a user id and
password. However, when I was installing the system (so I could access
the tables directly) and setting up the ODBC connection I had to user a
'generic' long userid and password to connect to the database. My
personal id wouldn't work.

I have created an Access database and linked to these tables and worked
up some queries. However, the first time I come into the database and
try to access a linked table I get a SQL Server login screen telling me
that the connection failed. It then prompts me to login. I have to use
the (long) name & password that I originally used to install (my GUI
login doesn't work.)

Up until now, this has worked OK - I just have to retype in the login &
password when I first enter the database. But now I'm trying to figure
out if there is some way to store the password in the connection. (I'm
pretty good w/ access; used to know VBA really well; but a bit foggy
on SQL server connections).

My ultimate problem is I have a lovely little spreadsheet I built where
I can just plug in the database path & query and it will automatically
run the query and pull the results into the spreadsheet. (Very handy
for my database-frightened coworkers.) However, I am unable to connect
to this Access database b/c my ODBC connection always fails b/c I don't
get to a place to put in my password.

If I can't store the name & pass w/ the connection, I'm wondering if I
could put it into the Excel VBA. The only catch is I am not connecting
directly to the SQL Server - instead I'm connecting to the Access
Database which has a rather complex set of queries needed to transform
the data into something I can use.

Phew! Am I using completely the wrong approach here? Frankly, I'm
probably going to HAVE to do my work in Access & present it in Excel.
I suppose I could flip it around & write VBA in Access to automatically
populate my excel reports (ie a push instead of a pull so to speak).
Then I would just login to the Access dbase, run my code, and make the
excel pop up.

But I suspect there is some crucial tidbit of knowledge that I just
need to learn about ODBC & connections & SQL Server to make my original
solution work.

Thank you so much for your time in reading this!
Lori

 

[Ron Hinds]

Yes, there *is* a crucial piece of info you are missing (I think anyway,
based on what you've stated). You need to link your tables using a Trusted
Connection in Access. In the ODBC manager, you need to make sure you choose
the option "With Windows NT authentication using the network login ID." when
you set up your DSN.

 

[Van T. Dinh]

I am not sure what you meant by "'generic' long userid and password" and
"personal id" but the settings on the SQLServer dictates what is the correct
UserName and password.
SQLServer can use SQLServer Authentication or Windows (NT) Authentication or
both. You will need to check with the SQL Server Admin to see which
authentication mode you can use.

In the SQLServer authentication, you must use the SQLServer Login and
SQLServer Login Password.

In Windows (NT) Authentication, (which is also known as Windows Pass-Through
Authentication because the user identification is passed through from the
Windows network), you need to log in to the normal Windows network and
SQLServer can identify you through your normal network UserName
automatically.

Either way, it seems that your ODBC DSN (Data Source Name) or the
ODBC-linked Connection String is not set correctly. Use Start / Control
Panel / Administrative Tools / Data Sources (ODBC) or Start / Programs /
Administrative Tools / Data Sources (ODBC) if this menu is enabled to modify
the setting of the DSN as required.

AFAIK, Excel can access SQLServer data directly (through the same DSN) so if
you only need to do the work in Excel, there is no point in accessing the
SQLServer data from Excel via Access ... Going through an intermediate will
simply creates complications and more places for the whole process to break
down.

 

[Guest]

Hey Lori -- went thru the exact same problem. Even if you make a work thru
for Acess -- SQL -- Excel with your login another user may get bumped (I
found out the hard way after making a business dashboard in Excel for a
manager). My suggestion:
1. Get your network admin to set up the trusted connection for you and
anyone you want to see the spreadsheet.
2. Play with your query in Access until you get it right the change to the
sql view.
3. Go into Excel and data>>get external data and attach the sheet to the SQL
tables.
4. paste the SQL statement into the query and save (access's conversion
sometimes needs some twicking)

Good luck.

 

[LoriK]

Hey Guys,

Thanks for all the helpful advice. Here is what I've figured out so
far:

Currently, I'm only set up w/ the SQL server authentication. What is
strange is that my login into the GUI doesn't work when connecting via
ODBC, so I guess that is another creature altogether (part of the GUI
rather than the dbase itself). I am sending a request to our IT fellow
to see if we can get me set up w/ the windows authentication. So I
think you are right, I'm just not setup properly.

The irony is that it defaults to a trusted connection w/ my network
login name, but entering my network password doesn't work. Which is
why I have to uncheck the box & then manually type in the SQL Server
login & password. Frankly, I'm not sure the database fellow knows I
was accessing it from the backend - I fast-talked a naive helpdesk
person into sending me the install information/login/pass <<grin>> So
here's hoping it doesn't get me 86-ed from the database entirely!

I *have* to go through Access b/c I have to do a series of queries and
logic and the like that is just beyond the capabilities of the query
thingy in Excel.

And I suppose I'm going to have to go w/ Ian's advice & get everyone
set up to the database. See, I was hoping I could hide some VBA in the
Excel that would login to Access and enter the password data. So
people could access the query/report I wanted them too. (But of
course, I'd lock everything down so they couldn't see the pass or
access other data even though it isn't _that_ confidential.) That way
I wouldn't have to personally refresh the report whenever we needed a
new one. However, I think IT is going to balk at letting other people
besides me having a login to the dbase.

Van: thanks for your detailed explanation - I didn't understand about
the 2 kinds of logins and your explanation helped.

Guys, I really do appreciate the time y'all took to reply to my
question. If any of y'all come out the Tulsa way let me know & I'll
kick you a freebie (I work in the casino biz.)

Thanks!
Lori

 

[Van T. Dinh]

I think you need to do the following in sequence:

1. Check with the SQLServer Admin to see if the authetication on the
SQLServer accepts SQL Authentication or Windows Pass-Through Authentication
or both.

Ask the SQLServer Admin the (communication) protocols that the SQLServer
communicates on, too. If it is TCP/IP, you may need the Port No, usually
1433.

2. Check with SQLServer Admin whether you have been set up as a user on the
SQL Server and whether you have been set up as SQLServer-authenticated user
or Windows-Pass-Through-authenticated user.

If you have been set up as SQLServer-authenticated user, you need the the
SQLServer LogInID and the SQLServer Password for the LogInID.

3. If you have been set up as SQLServer-authenticated user, you need to
enter the SQLServer LogInID/password in the configuration of the ODBC DSN.

4. If you have been set up as Windows-Pass-Through-authenticated user,
DON'T enter Windows UserName or Windows Password but make sure the trusted
connection is checked.

5. Either way, you also need to check the Client Configuration and make
sure that your work-station communicates with the SQLServer using one of the
acceptable protocols ...

OTOH, you I.T. Support group should be able to set this up for you. This
should be a fairly common job for them rather than you have to work it out
without enough details from the network side.

 

[LoriK]

Getting myself setup as a trusted user via Windows Login did the job.
(Now I'm trying to convince security that it is OK for my employees to
have access.)

I never did figure out how to code the password in VBA but I'll leave
that for another day.

I do have to go through Access b/c I have to combine tables from other
data sources and do a whole series of queries that build and it is too
complex for Excel's queries.

Thank you fellows for your help!

 

[Homer J Simpson]

Is it possible to get MS Access 2000 or 2003 to connect to a SQL Server
Express database on another machine on the network? All machines are running
WinXP.

 

[Van T. Dinh]

I haven't tried with SQL Server Express 2005 but I have a user connected to
the MSDE installed on my desktop (for testing purposes) so my guess is that
you should be able to. The Front-End in this set-up uses both linked Tables
(DSN-less ODBC connection) as well as SQL Server OLEDB Connection String in
VBA code.