间歇性ODBC连接失败 [英] Intermittent ODBC connection failures
问题描述
我们正在开发内部32位应用程序.该应用程序连接到SQL Server.测试环境为SQL Server 2008 R2，实时环境为SQL Server 2014 SP2.
We are developing an in-house 32bit app. The app connects to a SQL Server. The test environment is SQL Server 2008 R2, and the live environment is SQL Server 2014 SP2.
使用以下ODBC字符串建立与数据库的连接:
Connection to the database is made using this ODBC string:
Driver={SQL Server};Server=<redacted>;Database=<redacted>;Trusted_Connection=Yes;
此数据库的用户属于本地域"安全组.我们有来自三个不同AD域的用户作为该组的成员.该组定义为SQL Server的登录名和目标数据库的用户.在测试环境中，这很好.
The users for this database belong to a 'domain local' security group. We have users from three different AD domains as members of this group. The group is defined as a login for the SQL Server and a user for the target database. In the test environment, this worked fine.
在实时环境中，只有部分用户可以成功连接到数据库.有趣的是，属于安全组但与服务器不在同一域中的用户可以连接.与服务器位于同一域中的用户不能. (我尚未与所有25个用户进行核对，但这就是我所看到的模式).对数据库服务器具有sysadmin权限的用户(像我一样)没有任何麻烦.
In the live environment, only some of the users can successfully connect to the database. Interestingly, users who are in the security group but NOT in the same domain as the server can connect. Users in the same domain as the server cannot. (I've not checked with all 25 users, but that's the pattern I'm seeing). Users (like me) with sysadmin rights to the database server have no trouble.
我已经用细齿梳仔细研究了所有我能想到的东西，试图弄清为什么它可以在一台服务器上运行而不能在另一台服务器上运行，但是没有运气.我曾尝试以用户身份删除该网上论坛&重新添加，将用户从群组中删除并重新加入，依此类推. SQL Server服务在两台服务器上的同一域帐户下运行.
I've been over everything I can think of with a fine tooth comb trying to figure out why it works on one server but not the other, with no luck. I've tried removing the group as a user & adding back, taking users out of the group and putting them back in, and so forth. The SQL Server service is running under the same domain account on both servers.
任何见识都会受到赞赏.
Any insight is appreciated.
推荐答案
在过去，我遇到了很多问题，以下工作可行:
I've had numerous issues with this in the past and the following worked:
- 导航至开始| Microsoft SQL Server 2014年| SQL Server 2014配置管理器.
展开"SQL Server网络配置"，然后单击2008实例.完成此操作后，您将在右侧看到共享内存，命名管道和TCP/IP.打开命名管道"并启用它(如果已禁用).打开TCP/IP并启用它，但是在TCP/IP上，您还将注意到一个名为IP Addresses的辅助选项卡.单击此选项卡，然后分别为所有IP地址(或仅要激活的IP地址)启用每个IP地址. 您还将注意到，存在一个TCP端口，其中SQL的默认端口为1433.但是，当您在同一台计算机上安装多个SQL实例时，我通常会拆分端口号.即第一个实例设置为1433，第二个实例设置为1435.请参见下面的屏幕截图:
Expand SQL Server Network Configuration, then click on the 2008 instance. Upon doing that, on your right, you will notice Shared Memory, Named Pipes and TCP/IP. Open Named Pipes and Enable it, if it's disabled. Open TCP/IP and enable it, but on TCP/IP, you will also notice a secondary tab called IP Addresses. Click on this tab and enable each IP Address individually for all IP Addresses (or only the ones you want to activate). You will also note that there is a TCP Port of which the default port for SQL is 1433. However, when you have more than one instance of SQL installed on the same machine, I usually split the port numbers. i.e. the one instance, I set to 1433 and the second, I set to 1435. See screenshot below:
请注意，我删除了TCP动态端口中的零.
Notice that I removed the zero in the TCP Dynamic Ports.
到达最后时，像下面的屏幕截图一样设置它:
When you get to the end, set it like the following screenshot:
完成所有这些设置后，单击应用"，单击确定"，然后单击确定".
When you finished setting all this up, click on Apply, OK and then OK.
- 导航至开始|运行.
输入cliconfg(您必须在每台有连接问题的计算机上执行此操作)，然后单击确定".
Type in cliconfg (You'll have to do this on each machine that has issues connecting), then click OK.
启用命名管道和TCP/IP，然后勾选启用共享内存协议选项，如下所示:
Enable Named Pipes and TCP/IP, then tick the option Enable Shared Memory Protocol as can be seen below:
单击应用"，然后单击确定".
Click on Apply and then OK.
- 导航至开始运行"，键入services.msc，然后单击确定".
导航到两个SQL Server实例，然后重新启动它们.
Navigate to both instances of SQL Server and restart them.
还要打开SQL Server浏览器，将其从禁用"更改为自动"，然后输入您的AD用户名和&密码.
Also open SQL Server Browser, change it from Disabled to Automatic, then type in your AD Username & Password.
单击应用"并单击确定"，然后启动浏览器.
Click on Apply and OK, then start the Browser.
如果在此之后仍然存在连接问题，请尝试禁用Windows防火墙或按照答案中前面所述添加端口例外.
If you still have connection issues after this, try disabling Windows Firewall's or adding Port Exceptions as mentioned earlier in the answer.
如果您仍然有问题，请回复此答案，我们将为您提供进一步的帮助...
If you still have issues, reply to this answer and I will assist you further...
在工作站上，单击开始" |开始".运行，然后输入 cliconfg ，然后单击确定".
On the workstations, click on Start | Run, then type in cliconfg and click OK.
启用TCP/IP和命名管道，然后在选项 Enable Shared Memory Protocol (启用共享内存协议)上打钩，然后单击确定".
Enable TCP/IP and Named Pipes, then tick the option Enable Shared Memory Protocol and click OK.
希望这会有所帮助.
这篇关于间歇性ODBC连接失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
