Implementing remote connection access to a SQL Server database

In the same network environment, or under an IP address that can be accessed, we can use the SQL database for remote connection access using the following methods:

Create an access connection by calling Master.sys.sp_addlinkedserver

EXEC master.sys.sp_addlinkedserver @server = NULL,--sysname @srvproduct = n ",--nvarchar (+) @provider = n",- -nvarchar (+) @datasrc = n ",--nvarchar (4000) @location = n",--nvarchar (4000) @provstr = n ",--nvarchar ( 4000) @catalog = NULL--sysname

[  @server =  ]  Server
the name of the linked server to be created. &NBSP Server   has a data type of  sysname , no default value.

[  @srvproduct =  ]  Product_Name "
The product name of the OLE DB data source to be added as a linked server.   product_name   has a data type of  nvarchar ( 128) , the default value is NULL. If  sql Server , you do not have to specify   provider_name , data_source , location , provider_string   and   catalog .

[  @provider =  ]  provider_name " The
Unique programmatic identifier (PROGID) of the OLE DB provider that corresponds to this data source. provider_name   must be unique for the specified OLE DB provider that is installed on the current computer.   provider_name   has a data type of  nvarchar ( 128, the default value is NULL, but if &NBSP is omitted, provider_name , SQLNCLI is used. (using SQLNCLI and SQL Server is redirected to the latest version of the SQL Server Native Client OLE DB provider.) The OLE DB provider should be registered in the registry with the specified PROGID.

[  @datasrc =  ]  Data_source " The
name of the data source interpreted by the OLE DB provider.   data_source   has a data type of  nvarchar ( 4000) .   data_source   passed as the DBPROP_INIT_DATASOURCE property to initialize the OLE DB provider.

[  @location =  ]  location " The
location of the database interpreted by the OLE DB provider.   location   data type  ) , the default value is NULL.   location   is passed as the Dbprop_init_location property to initialize the OLE DB provider.

[  @provstr =  ]  provider_string " An
OLE DB provider-specific connection string that identifies a unique data source.   provider_string   has a data type of  nvarchar ( 4000) , the default value is NULL.   provstr   or passed to IDataInitialize or set to the Dbprop_init_providerstring property to initialize the OLE DB provider.

When creating a linked server for the SQL Server Native Client OLE DB provider, you can use the Server keyword to specify an instance, that is, using server=servername\instancename specifies a specific instance of SQL Server. servername is the name of the computer that is running SQL Server, andinstancename is the name of the specific instance of SQL Server that the user wants to connect to

Login to remote server database after successfully creating access link

exec master.sys.sp_addlinkedsrvlogin  @rmtsrvname  =  null, -- sysname     @useself  =  ",  -- varchar (8)       @locallogin  = NULL, -- sysname     @rmtuser  =  null, -- sysname     @rmtpassword  = null -- sysname 

[@rmtsrvname   rmtsrvname "
The name of the linked server where the login mapping is applied.   rmtsrvname   has a data type of  sysname , no default value.

[@useself   True"  | ' FALSE ' | ' NULL '
determines whether to connect to   by impersonating a local login or explicitly committing a login and password; rmtsrvname . The data type is  varchar ( 8, and the default value is TRUE.

rmtsrvname , ignoring   rmtuser   and   Rmtpassword   parameters. FALSE to specify   rmtuser   and   rmtpassword   parameters used to connect to the specified   locallogin     rmtsrvname . if   rmtuser   and   Rmtpassword   is also set to NULL, you do not use a login or password to connect to the linked server.

[@locallogin   LocalLogin "
Logon on the local server.   locallogin   has a data type of  sysname , the default value is NULL. NULL Specifies that this entry applies to all local logins that are connected to   rmtsrvname  . If not null, the   locallogin   can be either SQL Server logon or Windows logon. For Windows logins, they must be granted access to SQL Server either directly or through Windows group memberships that have been granted access.

[@rmtuser   Rmtuser "
The remote login name used to connect to   rmtsrvname   when @useself is FALSE. When the remote server is an instance of SQL Server that does not use Windows authentication, rmtuser is a SQL Server login name.   rmtuser   has a data type of  sysname , the default value is NULL.

[@rmtpassword = ] ' Rmtpassword '
The password associated with the rmtuser . The rmtpassword data type is sysnameand the default value is NULL.

Next, set the server options for the remote server and the linked server

EXEC master.sys.sp_serveroption @server = NULL,--sysname @optname = ",--varchar (+) @optvalue = N"-- nvarchar (128)

[ @server = ] ' Server '
The name of the server for which you want to set options. The server has a data type of sysnameand no default value.

[  @optname =  ]  option_name "
The options that are set for the specified server.   option_name   has a data type of  varchar ( 35) , no default value.   option_name   can be any of the following values.

[  @optvalue = ]  Option_value
Specifies that it should be enabled (true   or  on ) or disabled (false   or  off ) option_name .   option_value   has a data type of  varchar ( 10) , no default value.

The option_value can be a non-negative integer for connect timeout and the query timeout option. For the collation name option,option_value can be a collation name or NULL.

The above steps will enable access to the remote database.

Specific examples are as follows:

EXEC master.sys.sp_addlinkedserver@server = ' HQDB2 ', @srvproduct = ', @provider = ' SQLOLEDB ', @datasrc = ' 10.82.21.69 '  EXEC master.sys.sp_addlinkedsrvlogin @rmtsrvname = ' HQDB2 ', @useself = N ' false ', @locallogin = NULL, @rmtuser     = ' Btscn ', @rmtpassword = ' btscn ' EXEC master.sys.sp_serveroption @server = ' HQDB2 ', @optname = ' RPC out ', @optvalue = N ' true ' EXECUTE [HQDB2]. [BTSHQ9903]. [dbo]. [Stp_generate_rentalreport] @GenerateDate

A select * from Sys.servers can be executed to query whether the current remote access link exists

When the remote connection is finished, you need to release it

EXEC master.sys.sp_dropserver @server = NULL,--sysname @droplogins = "--char (10)

[ @server = ] ' Server '
The server to be removed. The server has a data type of sysnameand no default value. The server must exist.

[ @droplogins = ] ' droplogins ' | Null
Indicates that if droploginsis specified, the associated remote server and linked server logins must also be deleted for the server. The @droplogins data type is char (TEN)and the default value is NULL.

Instance:

EXEC master.sys.sp_dropserver @server = ' HQDB2 ', @droplogins = ' droplogins '

Need children's shoes can refer to, wish June Wu Yunchang long.

This article is from the "to IS EX" blog, please make sure to keep this source http://gleolee.blog.51cto.com/10632675/1789039

Implementing remote connection access to a SQL Server database