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.
Create Endpoint for database mirroring
Encryption type RC4 is the default algorithm.
Exam the endpoint information. Please see http://ocpbbc.blogspot.com/2011/11/database-mirroring.html for more detail about database mirroring.
Create user endpoint
You may also want to grant the DEFAULT connection to the public as well. Otherwise, you will lost the connectivity for non sysadmin login.
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
Create http endpoint
SQL Server Endpoints: Soup to Nuts http://www.simple-talk.com/sql/database-administration/sql-server-endpoints-soup-to-nuts/