How to create a generic data source connection for Excel to use SQL Server stored procedures?

We are converting about 300 reports from SSRS to Excel.  Is there a concept in Excel that equates to the SSRS shared data sources?  

It seems we need to define a new data connection for each report and stored procedure.

Is it possible to have 1 data connection (with the data base and userid/password defined) and share it with all the reports that use stored procedures from the database so all the excel report needs is the stored procedure name?
phoobarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

psteffCommented:
If all the reports are accessing the same database, then you should be able to set up one ODBC DSN that all the reports can connect to the database with.
phoobarAuthor Commented:
Excel seems to require the password for the account we're using for reporting in every file that uses the connection.  Is this right?
psteffCommented:
Depending on what type of environment and how it is set up.  We use Windows - the SQL database permissions are set up to use Windows authentication and we set up our DSN to use Windows authentication also.  So once, it is set up this way (if you are using Windows and a SQL database), then I would think it would work for you to not have to enter the password each time.  Seems to work well for me.  If you need to, perhaps you can get your DB admin to set it up this way for you.

If you can't get it set up this way, then I think you should be able to put the password in the connection string.  However, I would not recommend this because the password to the database could be seen by anyone who would use your Excel file.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.