Excel - Microsoft Query - SQL Server login - "Use Trusted Connection" default setting

Question

Couple of years using this site is as an invaluable resource, but first time posting.

I am having a little trouble with external data connections in Excel, specifically connecting to SQL Server through Microsoft Query.

Whenever I click

[Get External Data > 
     From Other Sources > 
          From Microsoft Query > 
               Choose Data Source: SQL Server]

it takes about 20 seconds before an error pops up:

Error 18452 Login Failed Untrusted Domain

After I click OK a SQL Server Login dialog box pops up with "Use Trusted Connection" pre-checked. Each time I create a new data connection in Excel (I do this dozens of times per project), I have to uncheck that box and enter a login ID/pw instead for it to work. Add all those 20 seconds up and it's actually a pretty big annoyance for the type of work I do.

My question: how do I change the connection properties to where "Use Trusted Connection" is unchecked by default and the Login ID and Password fields are pre-filled?

Here is what the connection string portion of the HTML code looks like when I right-click the appropriate .odc file in [Documents > My Data Sources] and click 'edit in notepad':

<odc:ConnectionString>Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xx_xxxxxx;Password=xxxxxxxx;Data Source=XX.XX.XX.XX;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=XXXXXXX;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Data_Warehouse</odc:ConnectionString>

I did look through the archives and found a few related discussions but none that addressed this question specifically. Thanks for your help. Please excuse any incorrect use of syntax and I hope I explained things clearly enough for a newbie.

Mike C · Accepted Answer · 2016-06-16 20:11:54Z

1

As luck would have it, only after spending a bit of time researching and asking the question did I figure out the embarassingly simple answer...

The login selection defaults to whatever was chose when the connection was first created. All I had to do was create a new connection and enter the credentials with "Use Trusted Connection" unchecked. I set the original connection up in Excel almost 2 years ago when I first joined my company and must have left "Use Trusted Connection" checked without really knowing what I was doing. Which is the reason why the error kept popping up after the connection initialized and failed, at which point I had to uncheck the box and try again. Silly me. #smh

edited Jun 16, 2016 at 20:11

answered Jun 16, 2016 at 19:56

Mike C

111 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel - Microsoft Query - SQL Server login - "Use Trusted Connection" default setting

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related