SQL Server Endpoints

A SQL Server endpoint is the point of entry into SQL Server. It is implemented as a database object that defines the ways and means  in which SQL Server may communicate over the network. SQL Server 2005 routes all interactions with the network via endpoints and each endpoint supports a specific type of communication.

A SQL Server endpoint is a useful point where one can enhance the security of a SQL Server 2005 installation. If, for example, you want to allow your DBAs to monitor a production database from afar then you'll need to set up remote access to a Server via the internet that only your support team can use. This is where endpoints come in. Endpoints, which are a general term for the point of connection between a client or server and the network, can be used in SQL Server in much the same way as a firewall, in order to limit the type of traffic to just what you, as administrator, expect and want. The advantage of a user-defined endpoint is that traffic must be authorised before it even reaches SQL Server. If you are implementing SOAP-based services, mirroring or Service Broker, then you are probably already up to your elbows in endpoints. They are essential for security.

When SQL Server 2005 is installed, a number of 'system endpoints' are set up in the MASTER database. You can start or stop these endpoints using the Surface Configuration tool. If an endpoint is stopped, it listens for, but rejects and closes new connections. These system endpoints provide a system that works in a manner similar to previous versions of SQL Server. You will not see the advantages of endpoints until you create your own. If you wish to set up an HTTP service such as SOAP, then you will need to set up an additional User endpoint to do it. The same is true when you are setting up Database Mirroring or Service Broker. You can define, alter, delete or reconfigure any number of user endpoints by using TSQL statements: You can also disable user endpoints. If a user endpoint is disabled, it acts as if it doesn't exist.

An endpoint has a transport, which will either be HTTP or TCP. You also specify a 'payload', which is one of TSQL, Service_Broker, Database_Mirroring, or SOAP. SOAP must use HTTP, and the others must use TCP. The endpoints, other than TSQL, have a number of special-purpose properties that define the way that the service using them communicates, and operates.

A SQL Server login must have permission to use an endpoint. (a CONNECT permission). By default, all PUBLIC groups have permission to use the default TCP connection. There is a 'Dedicated Admin Connection' endpoint that can only be used by members of the SysAdmin role. In order to tie down access security as much as possible, the DBA will be interested in replacing the implicit permission to access the other TDS endpoints to all users, with something more precise..

System Endpoints

When SQL Server is installed, a 'system endpoint' is created for each of the four protocols (TCP/IP, Shared Memory, Named Pipe, VIA) that accept TDS connections. The public group is given connection rights to all these, which allows all logins defined on the server to use these endpoints.. An additional system endpoint is created for the Dedicated Administrator Connection (DAC), which can only be used by members of the sysadmin fixed server role. These endpoints cannot be dropped or disabled, but you can can stop and start them. Additionally, the state can be changed via the TSQL 'ALTER ENDPOINT' DDL. When looking at endpoints via DMVs, one can distinguish system endpoints since they have an ID less than 65536. Because these endpoints are created internally by the server, they have no owner and you cannot associate them with a specific account.

The SQL Server Configuration Manager is the easiest way to alter the properties of the system endpoints. The settings for the TDS endpoints are recorded in the registry. However, one should only use Transact-SQL statements to create endpoints, and use SQL Server Configuration Manager to enable or disable protocols, which, in turn, starts and stops the endpoints.

Creating User Endpoints

Endpoints can be created and managed and dropped with CREATE ENDPOINT, ALTER ENDPOINT and DROP ENDPOINT statements. (Not, unfortunately in SQL Server Express). There are other statements such as GRANT CONNECT that are used to control or and take ownership of endpoints. Once you have created an endpoint, you will need to give CONNECT permission to the logins that are being used by the client to access SQL Server, and you may need to restore PUBLIC access to the default endpoint for the payload if appropriate.

TCP Endpoints

These are configured to listen on specific port numbers and server IP addresses. The system endpoint for TCP is configured to use port 1433 for backward-compatibility. Other ports can be used. The TCP endpoint can also be forced to listen for requests from just one IP address rather than all. Once you create a new endpoint, the public permission for connection to the TCP system endpoint is dropped. To create a TCP TDS endpoint called MyFirstUserConnection on port 1680 for all the available TCP addresses on the server.

CREATE ENDPOINT [MyFirstUserConnection]
STATE = STARTED
AS TCP
   (LISTENER_PORT = 1680, LISTENER_IP =ALL)
FOR TSQL() ;
GO


To grant access to this MyFirstUserConnection endpoint to the Support group in the MyFirm domain.

GRANT CONNECT ON ENDPOINT::[ MyFirstUserConnection] TO [MyFirm\Support] ;


If you want a system endpoint to listen on an additional TCP port, you can use SQL Server Configuration Manager to do so.

First expand 'SQL Server 2005 Network Configuration' in the left-side tree

• Click 'Protocols for '.
• Expand 'Protocols for ', and right-click TCP/IP.Select 'Properties'
• In the 'IP Addresses' tab of the properties dialog box, click each disabled IP address that you want to enable, and then click Enable.
• select the IPAll entry in the list,
• Type in a comma-separated list of all the ports that you want the Database Engine to listen on, in the TCP Port box. If you want to specify particular IP addresses, rather than use all of them, right-click TCP/IP in the console pane, click Properties, select the 'protocol tab, and, select No in the 'Listen All' box
• In the left pane, click 'SQL Server 2005 Services'.
• In the right pane, right-click 'SQL Server < MyInstance>', and then click 'Restart'.
• When the Database Engine restarts, the Error log will list the ports on which SQL Server is now listening.

For altering User TDS Endpoints, you will need to use TSQL as they do not show up in the Configuration Manager. However, once these are in place, they require little or no maintenance.

Database Mirroring and Service Broker Endpoints

SQL Server does not contain a Service Broker or Database Mirroring endpoint until you create one. You can create only one Service Broker, or Database mirroring endpoint on an instance. They use Transmission Control Protocol (TCP) to send and receive messages. Each endpoint listens on a unique TCP port number. The endpoint of a server instance controls the port on which that instance listens for messages from other server instances.

You can specify the authentication and encryption methods. Within a domain, or between trusted domains, Windows authentication is best; otherwise certificate-based authentication should be used. Strong encryption techniques will inevitably affect performance, so the default choice of RC4 is usually better than the stronger AES algorithm, unless you are operating in a relatively insecure network.

A Service Broker endpoint configures SQL Server to send and receive Service Broker messages over the network. Service Broker endpoints provide additional options for message forwarding.

The database mirroring endpoint of a server instance controls the port on which that instance listens for database mirroring messages from other server instances. Database Mirroring endpoints must also specify whether the endpoint should be a PARTNER, WITNESS or ALL. SQL Server Express can only be a witness.

The easiest way to set up Database Mirroring endpoints is to use the 'Configure Database Mirroring Security' Wizard, from the 'Configure Security' button on the Mirroring page of the Database Properties dialog in SSMS. But you can also execute the CREATE ENDPOINT command using Transact-SQL.

Here is an example of code to create a Database Mirroring endpoint

CREATE ENDPOINT endpoint_mirroring 
      STATE = STARTED AS TCP ( LISTENER_PORT = 7022 ) 
   
FOR DATABASE_MIRRORING 
     ( AUTHENTICATION = WINDOWS KERBEROS, 
       ENCRYPTION = SUPPORTED, 
       ROLE=ALL); 
GO

HTTP Endpoints

These are required for setting up a web service on SQL Server 2005. No default HTTP endpoint exists, but must be explicitly created and specified. These are more complex than the other types of endpoint because there are parameters for setting up Authentication method, Encryption, Login Type, Web Method, WSDL support and SOAP payload.

HTTP endpoints are created with a unique URL that they use to listen for incoming HTTP requests. SOAP requests that are sent to this URL will be routed by HTTP.SYS to the SQL Server instance that hosts the endpoint associated with the URL. From there, they are sent to the SOAP processing layer within SQL Server.

A SQL Server instance can have several endpoints, each of which can expose any number of stored procedures, as WebMethods on the endpoint. These WebMethods can be invoked via SOAP remote procedure calls. A WebMethod can have a different name than the actual stored procedure that is being exposed. The WebMethod name is what is shown to the user in WSDL as the operation name.

Users can be given permission to execute ad-hoc Transact-SQL statements against the endpoints by enabling batches on the endpoint. This results in a WebMethod named "sqlbatch" being exposed to the user.

All requests, including requests for WSDL, are authenticated. Clients must authenticate against SQL Server principals in order to submit any request. When setting up an HTTP endpoint, you will need to decide between Basic, Digest, Integrated (NTLM, Kerberos), and SQL Authentication. Any client can connect to a SQL Server Web Service by using either BASIC or SQL Auth. However, as BASIC requires the passwords to be sent over in clear text, users can connect only on secure ports that also have SSL enabled. (using the command httpcfg which ships with the support tools)

A connection firstly authenticates at the HTTP transport level. If successful, the user's SID is used to authenticate with SQL. The exception is SQL Auth. The SQL Auth credentials are sent as part of the SOAP packet using WsSecurity Username token headers. One can also restrict access to only specified IPs or ranges of IPs. Even if a stored procedure is mapped, it can only be executed if the user has CONNECT permissions on the endpoint as well as EXECUTE permissions on the stored procedure.

When an endpoint is created, only members of the sysadmin role and the owner of the endpoint can connect to the endpoint. You must grant connect permission for users to access your endpoint; this is accomplished by executing the following statement:

GRANT CONNECT ON HTTP ENDPOINT::MyLittleEndpoint TO [DOMAIN\USER] 

Securing a User Endpoint

To connect to an instance of SQL Server using Transact-SQL endpoints, users must have CONNECT permission to an endpoint and global permission on SQL Server to log in. When SQL Server is set up this will not be apparent because permission to connect to the default System endpoints is implicitly granted to users when logins are created.

When a new TCP endpoint is created, SQL Server automatically revokes all existing permissions on the TSQL Default TCP endpoint.

To restrict access to an endpoint, the administrator can deny permission to the EVERYONE group, using the DENY CONNECT statement. Then, he can grant permission to specific individuals or roles, using the GRANT CONNECT statement.

If one must return permissions to their original state, then GRANT CONNECT permission to the PUBLIC group.

To provide an endpoint exclusively for a specific application, DENY CONNECT permissions to all users, except the users for that application.

Altering a User Endpoint

The best and easiest way of inspecting or altering a simple system  TDS endpoint is with the SQL Server Configuration Manager. You can use the ALTER ENDPOINT Statement in TSQL to alter the properties of any endpoint. You need specify only those parameters that you want to update, and all other properties of an existing endpoint stay the same. The ENDPOINT DDL statements cannot be executed inside a user transaction.

Looking at endpoints

Endpoints can be inspected in one of the catalog views (see Endpoints Catalog Views (Transact-SQL))

e.g.

SELECT * FROM sys.endpoints

These catalog views are:

sys.endpoints	All endpoints and all generic properties
sys.database_mirroring_endpoints	The Database Mirroring endpoints
sys.service_broker_endpoints	The Service Broker endpoints
sys.soap_endpoints	HTTP endpoints that carry a SOAP-type payload
sys.endpoint_webmethods	SOAP methods defined on endpoints
sys.tcp_endpoints	All TCP endpoints and properties
sys.http_endpoints	All http endpoints and HTTP properties
sys.via_endpoints	All VIA endpoints and properties

Activity Monitor : #tmpDBCCinputbuffer

In Activity Monitor you see a mystery command, apparently executed by yourself, with 2 open transactions and coming from application 'Microsoft SQL Server Management Studio'.

Double clicking the row, gives you the Last Transact-SQL command batch >
" create table #tmpDBCCinputbuffer ([Event Type] nvarchar(512), [Parameters] int, [Event Info] nvarchar(512))
insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(319)')
select [Event Info] from #tmpDBCCinputbuffer "

Simple Answer is DONT PANIC!

It is simply the tsql generated by viewing process info in Activity Monitor itself.
The DBCC command you see is it fetching the information from the last time you double clicked an Activity Monitor row to see the last TSQL.

Of course, the irony of this is that by looking at the row, you've now changed the data.
You're looking at a system process row generated by looking at the system processes...

Can I use if statement in a table valued function?

No. Table valued function won't allow this. Because it might cause more than one schema definition for the table valued function, which is not possible in the database.

If it is a filter then you can attach the additional condition on the where clause. If the flag is used to fetch different columns or different table create new function for each flag.

If the final schema is same (same number of columns and identical datatype for both the flags), use table valued function (not inline table valued function).

Code Snippet

Create function getvalues(@flag as int)

Returns @result table (id int, name varchar(100))

As

Begin

            If @flag=1

                        Insert into @result

                        Select Top 10 id, name from sysobjects

            Else

                        Insert into @result

                        Select Top 10 id,name from syscolumns

 

            return;

End

 

Go

 

Select * from getvalues(1)

 

go

 

Select * from getvalues(0)

Origin of the name "Google"

From time to time I read or hear stories of the origin of the search engine and company name "Google" that are incorrect, which prompts me to write this brief account, based on my understanding of the genesis of the name. The source of my information is my friends and colleagues from Wing 3B of the Gates Computer Science Building at Stanford University, where Google was born.

In 1996, Larry Page and Sergey Brin called their initial search engine "BackRub," named for its analysis of the web's "back links." Larry's office was in room 360 of the Gates CS Building, which he shared with several other graduate students, including Sean Anderson, Tamara Munzner, and Lucas Pereira. In 1997, Larry and his officemates discussed a number of possible new names for the rapidly improving search technology. Sean recalls the final brainstorming session as occurring one day during September of that year.

Sean and Larry were in their office, using the whiteboard, trying to think up a good name - something that related to the indexing of an immense amount of data. Sean verbally suggested the word "googolplex," and Larry responded verbally with the shortened form, "googol" (both words refer to specific large numbers). Sean was seated at his computer terminal, so he executed a search of the Internet domain name registry database to see if the newly suggested name was still available for registration and use. Sean is not an infallible speller, and he made the mistake of searching for the name spelled as "google.com," which he found to be available. Larry liked the name, and within hours he took the step of registering the name "google.com" for himself and Sergey (the domain name registration record dates from September 15, 1997).