?
Solved

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

Posted on 2007-11-19
5
Medium Priority
?
276 Views
Last Modified: 2010-04-21
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
Comment
Question by:megnin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20314686
their gonna need read rights on the tables that refresh the excel file
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 20314698
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
 
LVL 42

Accepted Solution

by:
dqmq earned 2000 total points
ID: 20314705
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
 
LVL 31

Expert Comment

by:James Murrell
ID: 20314934
0
 
LVL 1

Author Closing Comment

by:megnin
ID: 31410004
Worked like a champ after the user rebooted her computer.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question