为什么我无法连接到 SQL Server 2012 LocalDB 共享实例? [英] Why can't I connect to a SQL Server 2012 LocalDB shared instance?

问题描述

我正在尝试在我的 Windows 7 x64 机器上设置一个 SQL Server 2012 LocalDB(RTM，x64)共享实例，但我似乎无法连接到共享实例.我正在使用管理员命令提示符进行所有设置.这是我创建实例的方式:

I'm trying to set up a SQL Server 2012 LocalDB (RTM, x64) shared instance on my Windows 7 x64 machine and I can't seem to connect to the shared instance. I'm using an Administrator command prompt for all of the setup. Here's how I'm creating the instance:

sqllocaldb create MyInstance

产生响应:

LocalDB instance "MyInstance" created with version 11.0.

到目前为止一切顺利.现在我共享实例:

So far so good. Now I share the instance:

sqllocaldb share "MyInstance" "MySharedInstance"

结果:

Private LocalDB instance "MyInstance" shared with the shared name: "MySharedInstance".

看起来还是不错的.此时，我的 info 命令产生了:

Still looking good. At this point, I the info command yields:

.MySharedInstance
MyInstance
v11.0

使用管理员或非管理员命令提示符从所有者帐户(管理员)连接到实例似乎工作正常.然而，当我以普通用户(不是 Windows 管理员)登录并尝试连接时，事情就偏离了轨道:

Connecting to the instance from the owner account (which is an admin) using both an admin or non-admin command prompt seems to work fine. Things come off the tracks, though, when I log in as a regular user (not a windows admin) and try to connect:

sqlcmd -S (localdb).MySharedInstance

结果:

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

使用-l"开关增加登录超时没有帮助.我可以连接到未共享的默认 v11.0 实例.非管理员用户的 info 命令产生与上面相同的结果，除了没有MyInstance"，因为它是管理员用户拥有的命名实例.以下命令(适用于管理员用户/实例所有者):

Increasing the login timeout using the "-l" switch does not help. I can connect to the default v11.0 instance, which is not shared. The info command for the non-admin user yields the same as above except withouth "MyInstance" since it's a named instance owned by the admin user. The following command (which works for the admin user/instance owner):

sqllocaldb info ".MySharedInstance"

也会导致错误:

Windows API call "FileTimeToSystemTime" returned error code: -2147024809.

那么问题是为什么我的非管理员用户无法连接到我的共享实例?这似乎违背了共享实例的全部目的.当我尝试查询共享实例时，sqllocaldb info"命令抛出错误是怎么回事?

So the question is why can't my non-admin user connect to my shared instance? This seems to defeat the whole purpose of shared instances. And what's with the "sqllocaldb info" command throwing an error when I try to query about the shared instance?

推荐答案

ANOTHER EDIT

Cory，如果您安装了以前版本的 SQL Server(例如 2008)，那就是您正在使用的 sqlcmd 版本.为了连接到 LocalDb，您需要使用 sqlcmd 的 SQL Server 2012 版本.因此，您对用户的说明必须通过运行以下命令来确保他们使用 SQL Server 2012 版本:

Cory, if you have previous versions of SQL Server installed (e.g. 2008), that is the version of sqlcmd you are using. In order to connect to LocalDb you need to be using the SQL Server 2012 version of sqlcmd. So your instructions to your users must ensure that they use the SQL Server 2012 version by running:

C:Program FilesMicrosoft SQL Server110ToolsBinnsqlcmd -S "(localdb).InstanceName"

这对我有用.我尚未验证的是，sqlcmd 的此路径和版本是否对仅 安装了 sqllocaldb.msi 的用户可用.抱歉，我没有任何未安装 SQL Server 2012(或仅安装以前版本)的裸机来彻底尝试一下.但是请让我知道是否明确调用 sqlcmd 的 110 版本可以解决问题.

This worked for me. What I haven't verified is whether this path and version of sqlcmd is available to users who have only installed the sqllocaldb.msi. Sorry but I don't have any naked machines without SQL Server 2012 installed (or with only previous versions installed) to try this out thoroughly. But please let me know if explicitly calling the 110 version of sqlcmd does the trick.

我认为您也可以指示用户更改他们的系统变量，以便 110 版本先出现(恕我直言应该是自动的).

I think you may also be able to instruct users to alter their system variables so that the 110 versions come first (which IMHO should be the case automatically).

FileTimeToSystemTime 已被 Krzysztof 的一位同事确认为错误.因此，我所知道的非所有者通过 sqllocaldb 连接仍然没有解决方法.但是我已经证明 SSMS 和 sqlcmd 都可以工作，所以我希望这能让你更接近运行.

The FileTimeToSystemTime has been confirmed as a bug by one of Krzysztof's co-workers. So there is still no fix that I know of for non-owners to connect via sqllocaldb. But I've shown that both SSMS and sqlcmd can be made to work, so I hope that gets you closer to running.

编辑

您需要将任何非所有者用户添加到实例中，例如CREATE LOGIN [MyDomainOtherUser] FROM WINDOWS; 以及任何适当的权限.在我的测试登录失败并生成错误的错误消息(FileTimeToSystemTime"错误消息是一个错误).您还需要GRANT CONNECT.完成此操作后，您将能够通过此连接(我尝试过的唯一一个)使用 Management Studio 从第二个用户进行连接:

You need to add any non-owner users to the instance, e.g. CREATE LOGIN [MyDomainOtherUser] FROM WINDOWS; and any appropriate permissions as well. In my test login was failing and generating the wrong error message (the "FileTimeToSystemTime" error message is a bug). You also need to GRANT CONNECT. Once you do this, you will be able to connect from the second user using Management Studio with this connection (the only one I tried):

(localdb).MySharedInstance

但是从 sqlcmd 开始，无论我如何尝试连接，我仍然收到错误:

But from sqlcmd, I still I get an error no matter how I try to connect:

sqlcmd -S "(localdb).MySharedInstance"
sqlcmd -S ".MySharedInstance"
sqlcmd -S "(localdb)MySharedInstance"
sqlcmd -S "GREENHORNETMySharedInstance"
sqlcmd -S ".LOCALDB#SH04FF8A"
sqlcmd -S "GREENHORNETLOCALDB#SH04FF8A"

所有产量:

HResult 0xFFFFFFFF，级别 16，状态 1 SQL Server 网络接口:

HResult 0xFFFFFFFF, Level 16, State 1 SQL Server Network Interfaces:

定位服务器/实例时出错 [xFFFFFFFF].

Error Locating Server/Instance Specified [xFFFFFFFF].

Sqlcmd:错误:Microsoft SQL Server Native Client 10.0:与网络相关的或建立连接时发生特定于实例的错误到 SQL Server.未找到或无法访问服务器.检查是否实例名称正确并且如果 SQL Server 配置为允许远程连接.有关详细信息，请参阅 SQL Server 联机丛书..

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

Sqlcmd:错误:Microsoft SQL Server Native Client 10.0:登录超时已过期.

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

虽然我已验证实例已设置为接受远程连接.因此，sqlcmd 必须经历其他一些事情.

Though I have verified that the instance is set to accept remote connections. So there is some other hoop that sqlcmd must be going through.

关于 sqllocaldb exe，这是如何遵循任何逻辑的?我可以通过 info 看到实例在那里，当我尝试停止它时我收到一条正确的错误消息，当我尝试启动它时我收到一条消息它[已经]启动，但是我无法连接?

And regarding the sqllocaldb exe, how does this follow any logic? I can see the instance is there via info, I get a proper error message when I try to stop it, I get a message that it is [already] started when I try to start it, but I can't connect to it?

因此，除非您需要 sqlcmd 访问权限，否则在短期内我会让次要用户使用 SSMS(一旦您授予足够的权限)并希望Krzysztof 将提供有关其他项目的更多信息.

So unless you need sqlcmd access, in the short term I would have the secondary users do their thing with SSMS (once you've granted adequate permissions) and hopefully Krzysztof will have more info on the other items.

关于 4.0.2 更新，来自 http://connect.microsoft.com/SQLServer/feedback/details/723737/smo-cant-connect-to-localdb-instances:

Regarding the 4.0.2 update, from http://connect.microsoft.com/SQLServer/feedback/details/723737/smo-cant-connect-to-localdb-instances:

我们明确决定不将 .NET Framework 4.0.2 包含在LocalDB 安装程序.安装 .NET Framework 更新会增加LocalDB 安装程序的大小并可能导致重新启动.自从LocalDB 被构建为独立于 .NET，我们不认为我们应该为每个 LocalDB 安装承担这个成本.未来.NET版本(包括 .NET 4.5，现在在 CTP 中)将支持 LocalDB盒子.一些开发人员可能还想选择 ODBC、PHP驱动程序/PDO，未来可能还有 JDBC.那些开发商不会有兴趣更新 .NET.

We made an explicit decision not to include .NET Framework 4.0.2 in LocalDB installer. Installing the .NET Framework update would increase the size of the LocalDB installer and cause a likely reboot. Since LocalDB is built to be independent of the .NET, we didn’t think we should take this cost for every LocalDB installation. Future .NET versions (including .NET 4.5, now in CTP) will support LocalDB out of the box. Some developers may also want to opt in for ODBC, PHP Driver/PDO, and probably JDBC in the future. Those developers will not be interested in updating .NET.

这篇关于为什么我无法连接到 SQL Server 2012 LocalDB 共享实例?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！