Excel - Microsoft Query - SQL Server登录 - “使用受信任的连接”默认设置 [英] Excel - Microsoft Query - SQL Server login - "Use Trusted Connection" default setting

问题描述

我在Excel中使用外部数据连接有一点麻烦，特别是连接通过Microsoft Query到SQL Server。

每当我点击

  [获取外部数据> 
来自其他来源> 
从Microsoft Query> 
选择数据源：SQL Server] 
  

大约需要20秒钟才会弹出错误：

错误18452登录失败不受信任的域

单击确定后，SQL Server登录对话框将弹出使用可信连接预先检查。每次我在Excel中创建一个新的数据连接（我每个项目这样做几十次），我必须取消选中该框并输入登录ID / pw，以使其正常工作。添加所有这些20秒，这对我做的工作类型来说实际上是一个很大的麻烦。

我的问题：如何将连接属性更改为Use默认情况下，受信任的连接未被选中，登录ID和密码字段是预先填充的。

这是连接字符串部分HTML代码看起来像是在[Documents> My Data Sources]中右键单击相应的.odc文件，然后单击在记事本中编辑：

 < odc：ConnectionString> Provider = SQLOLEDB.1; Persist Security Info = True; User ID = xx_xxxxxx; Password = xxxxxxxx;数据源= XX.XX.XX.XX; Prepare = 1; Auto Translate = True;数据包大小= 4096;工作站ID = XXXXXXX;对Data = False使用加密;可能时使用列归类的标记= False;初始目录= Data_Warehouse< / odc：ConnectionString> 
  

我查看了档案，发现了一些相关的讨论，但没有一个具体解决这个问题。谢谢你的帮助。请原谅任何不正确的使用语法，我希望我能为新手解释事情。

解决方案

幸运的是，只有在花了一点时间研究并提出这个问题后才发现这个令人难堪的简单答案...

登录选项默认为连接时选择的内容首先创建。我所做的只是创建一个新的连接，并输入凭据使用信任的连接未选中。近两年前，当我第一次加入我公司时，我将Excel中的原始连接设置为Excel，并且必须在不知道我在做什么的情况下离开使用信任的连接。这是连接初始化和失败后错误不断弹出的原因，此时我不得不取消选中该框，然后重试。我傻。 #smh

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.

解决方案

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

这篇关于Excel - Microsoft Query - SQL Server登录 - “使用受信任的连接”默认设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！