After SQL Server installation, MSSQL create below 5 endpoints by default.
- Dedicated Admin Connection
- TSQL Local Machine: Share memory
- TSQL Named Pipes: Name Pipes
- TSQL Default TCP: TCP/IP
- TSQL Default VIA: VIA
These endpoints is bases on TDS (Tabular Data Stream). It is an application layer protocol, used to transfer data between a database server and a client. It was initially designed and developed by Sybase. When MSFT adapt the Sybase server code. It is been port over to MS SQL Server. Since then MSFT has made many changes on it. You can also see other open source implementation base on the TDS spec such as FreeTDS and jTDS.
Views related to endpoints
We can use below tables to exam the endpoint information.
- use master;
- select * from sys.endpoints;
- select * from sys.tcp_endpoints;
- select * from sys.http_endpoints;
- select * from sys.database_mirroring_endpoints;
- select * from sys.service_broker_endpoints;
Create Endpoint for database mirroring
Encryption type RC4 is the default algorithm.
- CREATE ENDPOINT mirror
- STATE = STARTED
- AS TCP ( LISTENER_PORT = 7051 )
- FOR DATABASE_MIRRORING (ROLE=PARTNER,ENCRYPTION=REQUIRED ALGORITHM RC4);
- GO
Exam the endpoint information. Please see http://ocpbbc.blogspot.com/2011/11/database-mirroring.html for more detail about database mirroring.
- use master;
- SELECT * FROM sys.tcp_endpoints;
- select * from sys.database_mirroring_endpoints;
Create user endpoint
- CREATE ENDPOINT [SG1USERENDPOINT]
- STATE = STARTED
- AS TCP
- (LISTENER_PORT = 4536, LISTENER_IP =ALL)
- FOR TSQL() ;
- GO
- GRANT CONNECT ON ENDPOINT::[SG1USERENDPOINT] TO [testuser]
You may also want to grant the DEFAULT connection to the public as well. Otherwise, you will lost the connectivity for non sysadmin login.
- GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
Add the additional port and bounce the sql server
After server bounce, we can run netstate to confirm whether it is listening the new port number 4536
Lets test the connection from sqlcmd
Only Sysadmin or the login has been grant to use the endpoint can connect through this port number. [mytest] login does not been grant the permission, hence it get the login failed message.
Exam the endpoint permission for given user
- select* from sys.server_permissions where grantee_principal_id = suser_id('testuser')
Create http endpoint
- CREATE ENDPOINT AWProduction
- STATE = STARTED
- AS HTTP(
- -- Web Reference http://localhost/AdventureWorks/Production?wsdl
- PATH = '/AdventureWorks/Production',
- AUTHENTICATION = (INTEGRATED),
- PORTS = ( CLEAR ))
- FOR SOAP(
- WEBMETHOD 'GetProducts' (name='AdventureWorks.Production.GetProducts',FORMAT=ROWSETS_ONLY),
- WEBMETHOD 'UpdateProductPrice' (name='AdventureWorks.Production.UpdateProductPrice' ,schema=NONE),
- BATCHES = ENABLED,
- WSDL = DEFAULT,
- DATABASE = 'AdventureWorks',
- NAMESPACE = 'http://AdventureWorks/'
- )
Reference
SQL Server Endpoints: Soup to Nuts http://www.simple-talk.com/sql/database-administration/sql-server-endpoints-soup-to-nuts/
Rolex watches Replica Oyster Perpetual Submarinerare crafted from Replica Watches UK the finest raw materials and assembled with scrupulous attention to detail. Every component is designed, developed and produced in-house to the most exacting standards.
ReplyDelete