CODEWITHSUNDEEP
sql-serverexcel
Kortike
Kortike

Reputation: 37

Share Excel file with SQL Server connection on network

I have an Excel file with a SQL query to retrieve information from our SQL Server database. I want this Excel file to be available to anyone on the network.

I tested this a couple of times and it is not creating the connection on any PC but mine. I found a few answers that I thought would resolve the problem but whenever I attempt to change the connection string in Excel it reverts back to the original string.

Here is the current connection string:

DRIVER=SQL Server;SERVER=SERVER\SQLEXPRESS;UID=<My User ID>;Trusted_Connection=Yes;APP=2007 Microsoft Office system;WSID=<My WS ID>;DATABASE=<Database Name>

The main person I would like to have access to this file also has permissions to access the database from SQL Server Management Studio. What I found from searching said I should set the security to Windows Authentication and change the connection string to something along these lines:

DRIVER=SQL Server;SERVER=SERVER\SQLEXPRESS;Integrated Security=SSPI ;DATABASE=<Database name>

However when I attempt to change the connection settings in my Excel file it reverts back to the original string. What would be causing it to revert back to the original connection string after I change it and is the second example correct?

Thanks in advance

Upvotes: 1

Views: 1021

Answers (1)

Kortike
Kortike

Reputation: 37

I'll leave the question open since it could help someone else down the road. I got this to work by changing the connection string inside of the connection file to:

DRIVER=SQL Server;SERVER=SERVER\SQLEXPRESS;Integrated Security=SSPI ;DATABASE=<Database name>

The line Integrated Security=SSPI took care of the sharing issues between computers.

Upvotes: 1

Related Questions