I created an Excel 2017 worksheet with a Power Pivot data connection to a SQL Server on our corporate network. I want to let a coworker use the worksheet to view and refresh the data.
From the Data tab I selected New Query-> From Database ->From SQL Server Database
I then entered the server and database names and a custom query. I set up the connection using Windows credentials, Privacy Level None, Encryption Off. Then loaded the data into a worksheet tab.
I emailed the worksheet to my coworker. This user can run the query in the worksheet in SQL Management Studio so we know he has access to the database. When he tries to refresh the data in the worksheet it gives him an error : "We couldn't authenticate with the credentials provided". On his computer we selected Use Alternate Credentials and entered his windows login creds. We got he same error.
Thank you in advance for your assistance