SQL Server ip / port映射到第三方应用程序(SQL Server ip/port mapping to 3rd party app)

SQL Server ip / port映射到第三方应用程序(SQL Server ip/port mapping to 3rd party app)

不确定这是否可行，我有一个SQL Server 2012数据库，我需要将数据传输到特定IP /端口上的第三方应用程序。 这可能吗。 如果是这样，你如何'映射'到特定的IP /端口？ 试图避免编写中间件。 提前致谢。

Not sure if this is possible, I have a SQL Server 2012 database that I need to transmit data to a third-party app on a specific IP/port. Is this possible. If so, how do you 'map' to an specific ip/port? Trying to avoid writing middleware. Thanks in advance.

最满意答案

创建端点，例如， CREATE ENDPOINT MyEndpoint STATE = STARTED AS TCP (LISTENER_PORT = 50001, LISTENER_IP = ALL) FOR TSQL(); 。 如果要使用特定地址，则地址必须在括号中，例如，LISTENER_IP =（10.0.0.201）。 请注意，默认情况下，在创建新的TCP / IP端点时，将撤消对公共角色的默认TCP / IP端点的访问权限 使用GRANT / DENY CONNECT修改新端点上的访问权限 将LISTENER_PORT中指定的端口添加为SQL Server配置管理器中的新TCP / IP端口（即，WinKey + R / MMC /菜单文件/添加/删除管理单元/ SQL Server配置管理器/添加/确定/ SQL Server配置管理器） 重新启动SQL Server

根据您想要对用户端点执行的方案，有时让SQL Server执行直观的操作可能会非常棘手。 因此，测试用户端点很有用。 检查错误日志SQL Server正在侦听的IP和端口。 还尝试连接到SQL Server并验证您的连接是否正在使用所需的端点。 例如：

SELECT * FROM sys.endpoints WHERE endpoint_id = ( SELECT endpoint_id FROM sys.dm_exec_connections WHERE session_id = @@SPID; ); EXEC sys.sp_readerrorlog 0, 1, 'listening'; -- Returns only 'listening' rows

我希望它有所帮助。

Create the endpoint, e.g., CREATE ENDPOINT MyEndpoint STATE = STARTED AS TCP (LISTENER_PORT = 50001, LISTENER_IP = ALL) FOR TSQL();. If you want to use a specific address, the address must be in parentheses, e.g., LISTENER_IP = (10.0.0.201). Note that By default, access to the default TCP/IP endpoint is revoked for public role when a new TCP/IP endpoint is created Modify access on the new endpoint using GRANT / DENY CONNECT Add the port specified in the LISTENER_PORT as a new TCP/IP port in the SQL Server Configuration Manager (i.e., WinKey + R / MMC / menu File / Add/Remove Snap-in / SQL Server Configuration Manager / Add / O.K. / SQL Server Configuration Manager) Restart the SQL Server

Depending on the scenario you want to do with user endpoints, sometimes it can be very tricky to make the SQL Server do what intuitively should work without problems. Therefore, it’s useful to test the user endpoints. Check the error log what IPs and ports the SQL Server is listening on. Also try to connect to the SQL Server and verify that your connection is using the desired endpoint. E.g. :

SELECT * FROM sys.endpoints WHERE endpoint_id = ( SELECT endpoint_id FROM sys.dm_exec_connections WHERE session_id = @@SPID; ); EXEC sys.sp_readerrorlog 0, 1, 'listening'; -- Returns only 'listening' rows

I hope it helps.