Shulou(Shulou.com)05/31 Report--

Sql server database how to connect remotely, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, there are people who need this can learn, I hope you can gain something.

Under the same network environment, or under the IP address that can be accessed, we can use the SQL database to achieve 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(128) @provider = N'', -- nvarchar(128) @datasrc = N'', -- nvarchar(4000) @location = N'', -- nvarchar(4000) @provstr = N'', -- nvarchar(4000) @catalog = NULL -- sysname

[ @server= ] 'server'

The name of the linked server to create. The data type of server is sysname, no default value.

[ @srvproduct= ] 'product_name'

The product name of the OLE DB data source to add as a linked server. The data type of product_name is nvarchar(128) and the default value is NULL. If SQL Server, you do not need to specify provider_name, data_source, location, provider_string, and catalog.

[ @provider= ] 'provider_name'

The unique programming identifier (PROGID) of the OLE DB provider corresponding to this data source. Provider_name must be unique for the specified OLE DB provider installed on the current computer. The data type for provider_name is nvarchar(128) and the default value is NULL; however, if provider_name is omitted, SQLNCLI is used. (SQLNCLI is used and SQL Server redirects to the latest version of the SQL Server Native Client OLE DB provider.) OLE DB providers 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. The data type of data_source is nvarchar(4000). data_source is passed as the DBPROP_INIT_DATASOURCE attribute to initialize the OLE DB provider.

[ @location= ] 'location'

The location of the database interpreted by the OLE DB provider. The data type of location is nvarchar(4000), and the default value is NULL. location is passed as the DBPROP_INIT_LOCATION property to initialize the OLE DB provider.

[ @provstr= ] 'provider_string'

OLE DB provider specific connection string that identifies a unique data source. The data type of provider_string is nvarchar(4000) and the default value is NULL. provstr is either 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, SERVER=servername\instanrename to specify a specific SQL Server instance. servername is the name of the computer running SQL Server, instancename is the name of the specific SQL Server instance to which the user is connecting

After successfully creating the access link, you need to log in to the remote server database

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 to which the login mapping applies. The rmtsrvname data type is sysname and has no default value.

[ @useself = ] 'TRUE' | 'FALSE' | 'NULL'

Determines whether to connect to rmtsrvname by impersonating a local login or explicitly submitting a login and password. The data type is varchar(8) and the default value is TRUE.

A value of TRUE specifies that the login connects to rmtsrvname using its own credentials, ignoring the rmtuser and rmtpassword parameters. FALSE Specifies the rmtuser and rmtpassword parameters used to connect to the rmtsrvname of the specified locallogin. If rmtuser and rmtpassword are also set to NULL, the login or password is not used to connect to the linked server.

[ @locallogin = ] 'locallogin'

Login on local server. The data type of locallogin is sysname, and the default value is NULL. NULL Specifies that this item applies to all local logins connected to rmtsrvname. If not NULL, locallogin can be SQL Server login or Windows login. For Windows logins, access to SQL Server must be granted either directly or through Windows group membership to which access is granted.

[ @rmtuser = ] 'rmtuser'

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

[ @rmtpassword = ] 'rmtpassword'

Password associated with rmtuser. The data type of rmtpassword is sysname, and the default value is NULL.

The next step is to set server options for remote and linked servers

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

[ @server = ] 'server'

The name of the server for which you want to set options. The data type of server is sysname, no default value.

[ @optname = ] 'option_name'

Options set for the specified server. option_name has a data type of varchar(35) and no default value. option_name can be any of the following values.

[ @optvalue =] 'option_value'

Specifies whether option_name should be enabled (TRUE or on) or disabled (FALSE or off). option_value has a data type of varchar(10) and no default value.

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

Through the above steps, you can access 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

You can execute select * from sys.servers to query whether the current remote access link exists

Need to release when remote connection is used up

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

[ @server = ] 'server'

Server to delete. The data type of server is sysname, no default value. Server must exist.

[ @droplogins = ] 'droplogins' | NULL

Indicates that if droplogins is specified, the associated remote server and linked server logins must also be removed for server. @droplogins has a char(10) data type and a default value of NULL.

Examples:

EXEC master.sys.sp_dropserver @server = 'HQDB2', @droplogins = 'droplogins' Did reading the above help you? If you still want to have further understanding of related knowledge or read more related articles, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.