我需要将链接服务器添加到MS Azure SQL Server [英] I need to add a linked server to a MS Azure SQL Server
问题描述
我已经尝试了很多,但是无法链接.我可以使用SSMS连接到服务器,但是不能从本地服务器链接到该服务器.这是我的脚本(用相关信息替换括号中的内容):
I have tried and tried, and can not get linked. I can connect to the server using SSMS, but can not link to it from a local server. Here is my script (replacing things in brackets with pertainent information):
EXEC master.dbo.sp_addlinkedserver
@server = N'[servername].database.windows.net',
@srvproduct = N'Any',
@provider = N'MSDASQL',
@datasrc = N'Azure_ODBC1'
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'[servername]',
@useself = N'False',
@locallogin = NULL,
@rmtuser = N'[username]',
@rmtpassword = '[password]'
GO
推荐答案
如 ckarst 第二个链接中所指定的,有一种解决方案有效.我将其张贴在这里,以免您麻烦寻找它.正如 JuanPableJofre 在此页面中所建议的,
As specified in ckarst second link, there is a solution that works. I am posting it here to save you the trouble to search for it. As suggested by JuanPableJofre in this page Azure feedback :
使用SQL 2014,我能够在本地SQL服务器和SQL Azure之间进行分布式查询. 首先,我创建了一个链接服务器:
Using SQL 2014, I was able to do a distributed query between a local SQL server and a SQL Azure. First, I created a Linked-Server:
- 链接服务器(名称): LinkedServerName
- 提供程序:用于SQL Server的Microsoft OLE DB提供程序
- 产品名称:(空白)
- 数据源: azure-db .database.windows.net
- 提供程序字符串:(空白)
- 位置:(空白)
- 目录:数据库名称
- Linked Server (name): LinkedServerName
- Provider: Microsoft OLE DB Provider for SQL Server
- Product name: (blank)
- Data Source: azure-db.database.windows.net
- Provider string: (blank)
- Location: (blank)
- Catalog: db-name
- 使用此安全上下文制作
- 远程登录: azure用户名
- 使用密码: yourPassword
- Be made using this security context
- Remote login: azure-user-name
- With password: yourPassword
在安全选项中:(*)
In security options: (*)
在SSMS中输入以下测试查询:
In SSMS entered the following test query:
use [Local_DB]
go
Select *
from [LinkedServerName].[RemoteDB].[dbo].[Remote_Table]
效果很好!
It worked beautifully !!
总而言之,链接服务器是在本地数据库上创建的.目录(数据库名称)很重要,因为Azure可能不允许您在查询中指定它(即:使用azureDBName 在Azure上不起作用),因此数据库名称必须在目录中.
To summarize, the linked server is created on your local database. The catalog (database name) is important as Azure might not let you specify it in a query (ie: use azureDBName will not work on Azure), so the database name has to be in the catalog.
这篇关于我需要将链接服务器添加到MS Azure SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!