Users get Login Failure when opening Excel file with External Data Connection to SQL Server

I made a report in Excel 2003 with an External Data Connection to our SQL Server 2000.  The data connection is set to refresh when the file is opened.  It works fine for me because I have full access to the SQL Server and databases.
But when a user opens the worksheet it asks if they want to refresh external data and when they say "OK" they get a Login Failure.

How can I fix the Connection String or whatever I need to do so users can refresh the data?  

Thanks.
LVL 1
megninAsked:
Who is Participating?
 
dqmqCommented:
My recommendateion is to use windows authentication in your connection string.  Create a windows group for accessing the database and make everyone you want to have access a member of that group.  In Management Studio, add a database login for the group.      
0
 
SQL_SERVER_DBACommented:
their gonna need read rights on the tables that refresh the excel file
0
 
Saurabh Singh TeotiaCommented:
Well...Try defining your query through this...

Go to --->Data-->Import External Data-->Import Data...

As soon you click on it...It will take u to your data sources..where u find some options...

Inwhich one of the option will be
"New SQl Connection"...Select that and click okay...

a Window will open which will ask for in step-1 the server name.
and in step-2 u can define the user name and password by which u want to connect to sql..so everytime the query will use this id and password only.......

Hope this helps...
0
 
James MurrellProduct SpecialistCommented:
0
 
megninAuthor Commented:
Worked like a champ after the user rebooted her computer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.