1 Endpoints

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.

1. use master;
2. select * from sys.endpoints;
3. select * from sys.tcp_endpoints;
4. select * from sys.http_endpoints;
5. select * from sys.database_mirroring_endpoints;
6. select * from sys.service_broker_endpoints;

Create Endpoint for database mirroring

Encryption type RC4 is the default algorithm.

1. CREATE ENDPOINT mirror
2.     STATE = STARTED
3.     AS TCP ( LISTENER_PORT = 7051 )
4.     FOR DATABASE_MIRRORING (ROLE=PARTNER,ENCRYPTION=REQUIRED ALGORITHM RC4);
5. GO

Exam the endpoint information. Please see http://ocpbbc.blogspot.com/2011/11/database-mirroring.html for more detail about database mirroring.

1. use master;
2. SELECT * FROM sys.tcp_endpoints;
3. select * from sys.database_mirroring_endpoints;

Create user endpoint

1. CREATE ENDPOINT [SG1USERENDPOINT]
2. STATE = STARTED
3. AS TCP
4.    (LISTENER_PORT = 4536, LISTENER_IP =ALL)
5. FOR TSQL() ;
6. GO
7. 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.

1. 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

1. select* from sys.server_permissions where grantee_principal_id = suser_id('testuser')

Create http endpoint

 

1. CREATE ENDPOINT AWProduction
2. STATE = STARTED
3. AS HTTP(
4.     -- Web Reference  http://localhost/AdventureWorks/Production?wsdl
5.     PATH = '/AdventureWorks/Production',
6.     AUTHENTICATION = (INTEGRATED),
7.     PORTS = ( CLEAR ))
8. FOR SOAP(
9.     WEBMETHOD 'GetProducts' (name='AdventureWorks.Production.GetProducts',FORMAT=ROWSETS_ONLY),
10.     WEBMETHOD 'UpdateProductPrice' (name='AdventureWorks.Production.UpdateProductPrice' ,schema=NONE),
11.     BATCHES = ENABLED,
12.     WSDL = DEFAULT,
13.     DATABASE = 'AdventureWorks',
14.     NAMESPACE = 'http://AdventureWorks/'
15. )

Reference

SQL Server Endpoints: Soup to Nuts http://www.simple-talk.com/sql/database-administration/sql-server-endpoints-soup-to-nuts/