问题描述
我尝试从我的本地计算机上的SQL Server Management Studio访问我的VPS上的SQL Server实例。它不工作(我得到的错误是:
我认为这是因为我需要配置数据库引擎以允许远程连接(如果我错了,请纠正我)分步指南帮助我这样做:我必须指出10在我现在卡住了!我没有在我的VPS上安装SQL Server Management Studio。无论如何,这让我有两个选择:
- 安装SSMS
- 在指南中找到另一种执行点10的方法,但不安装SSMS
我尝试使用Web平台安装程序在我的VPS上安装SSMS,但它仍然失败。我不知道为什么它失败,因为它似乎没有给出一个理由为什么。有没有人知道我可以用不同的方式允许远程连接?
我的VPS上安装的SQL Server版本是SQL Server 2008 R2 Express。
更新:
我尝试禁用我的笔记本电脑和VPS上的防火墙,是一个防火墙问题。
有了这个错误讯息,这个错误消息就没有任何区别。
现在已经能够安装SSMS(我直接从网站安装而不是使用WPI),我已经能够检查服务器是否配置为允许远程连接(我去了SSMS,连接到SQL Server实例,点击连接,点击属性,进入连接选项卡。允许远程连接到此服务器已经勾选)。
感谢大家帮我解决这个问题!我终于设法让它工作!我按照Filip De Vos的建议,打开了防火墙中的端口在我的VPS,然后我收到一个不同的错误消息。这使我进一步调查,我发现我使用错误的凭据登录!所以我设置了sa用户的密码,我已经设法登录使用!
-
要启用混合身份验证,您可以更改以下注册表项:
HKLM \Software\Microsoft\ Microsoft Microsoft SQL Server \MSSQL.1\MSSQLServer\LoginMode
将值更新为2并重新启动Sql Server服务以允许混合身份验证。 请注意,MSSQL.1可能需要更新以反映您尝试更改的SQL Server实例的数量。
-
出现连接错误的原因可能是病毒扫描器安装在阻止
sqlserver.exe
的服务器上。 -
另一个原因可能是SQL Server Browser服务没有运行。当此服务未运行时,您无法在命名实例上连接(当他们使用动态端口时)。
-
也可能是Sql Server未设置为监听TCP连接,并且只允许命名管道。
- 在开始菜单中,打开程序> Microsoft SQL Server 2008>
配置工具> SQL Server表面区域配置 - 在Surface Area配置实用程序中,单击链接SQL Server
Configuration Manager - 展开SQL Server网络配置 b选择协议。
- 启用TCP / IP。如果你需要命名管道,那么你可以
在这里启用他们。
- 在开始菜单中,打开程序> Microsoft SQL Server 2008>
-
最后但并非最不重要的是,Windows防火墙需要允许连接到SQL Server
- 在使用动态端口系统时,为sqlserver.exe添加异常。
- 否则,您可以为SQL Server端口(默认端口1433)设置例外
- 还为SQL Server浏览器添加了一个例外。 (udp port 1434)
详情:
最后一点,只支持命名管道,因此您无法通过网络连接到它。
I’m trying to access the SQL Server instance on my VPS from SQL Server Management Studio on my local machine. It’s not working (the error I’m getting is:
I think this is because I need to configure the database engine to allow remote connections (correct me if I’m wrong!). So I’ve found this step-by-step guide to help me do that: http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/ I’ve got to point 10 in the guide and I am now stuck! I don’t have SQL Server Management Studio installed on my VPS. Anyway, this has left me with two options:
- Install SSMS
- Find another way to do point 10 onwards in the guide without having SSMS installed
I tried installing SSMS on my VPS using the Web Platform Installer but it keeps failing. I don’t know why it’s failing because it doesn’t seem to give a reason why. Does anyone know how I could allow remote connections a different way?
The version of SQL Server installed on my VPS is SQL Server 2008 R2 Express.
Update:
I have tried to disable the firewall on both my laptop and VPS to see if it is a firewall issue. This made no difference to the error message.
Another Update:
Having now been able to install SSMS (I installed directly from the website rather than using the WPI), I have been able to check that the server is configured to allow remote connections (I went to SSMS, connected to the SQL Server instance, right-clicked on the connection, clicked Properties, went to the Connections tab. "Allow remote connections to this server" is already ticked).
SOLUTION
Thanks to everyone for helping me get to this solution! I've finally managed to get it to work! I followed Filip De Vos's advice and opened the ports in the Firewall on my VPS and then I received a different error message. This led me to investigate further and I found that I was using the wrong credentials to login! So I've set a password for the sa user and I've managed to login using that! Thanks again!
To enable mixed authentication you can change the following registry key:
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\LoginMode
Update the value to 2 and restart the Sql Server service to allow mixed authentication. Note that MSSQL.1 might need to be updated to reflect the number of the SQL Server Instance you are attempting to change.
A reason for connection errors can be a virus scanner installed on the server which blocks
sqlserver.exe
.Another reason can be that the SQL Server Browser service is not running. When this service is not running you cannot connect on named instances (when they are using dynamic ports).
It is also possible that Sql Server is not setup to listen to TCP connections and only allows named pipes.
- In the Start Menu, open Programs > Microsoft SQL Server 2008 >Configuration Tools > SQL Server Surface Area Configuration
- In the Surface Area Configuration utility, click the link "SQL ServerConfiguration Manager"
- Expand "SQL Server Network Configuration" andselect Protocols.
- Enable TCP/IP. If you need Named Pipes, then you canenable them here as well.
Last but not least, the Windows firewall needs to allow connections to SQL Server
- Add an exception for sqlserver.exe when you use the "Dynamic Port" system.
- Otherwise you can put exceptions for the SQL Server ports (default port 1433)
- Also add an exception for the SQL Server Browser. (udp port 1434)
More information:
- How to: Configure a Windows Firewall for Database Engine Access
- Server Connectivity How-to Topics (Database Engine)
As a last note, SqlLocalDB only supports named pipes, so you can not connect to it over the network.
这篇关于无法远程连接到SQL Server实例的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!