Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

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.
0
megnin
Asked:
megnin
1 Solution
 
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
 
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
 
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.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now