Muokkaa

Jaa

Best practices to secure the SQL adapter

  • Artikkeli

Best practices that you should follow to more completely protect sensitive data when you use or develop applications that consume the Microsoft BizTalk Adapter for SQL Server.

Security Best Practices for the Connection between the SQL Adapter and the SQL Server Database

  • The SQL adapter provides no support for helping to secure communication between it and the SQL Server database. You must provide a mechanism to help ensure an adequate level of security for data exchanged between the adapter and the SQL Server database.

  • For security reasons, the SQL adapter does not allow you to provide user name password credentials for the SQL Server database in the connection URI. See the remainder of this topic for alternative methods of providing credentials to the SQL adapter.

  • The SQL adapter also enables you to use Windows Authentication while connecting to SQL Server to generate metadata and perform operations, either through Visual Studio or BizTalk Server. Before using Windows Authentication, you must add the Windows user as a user in SQL Server Management Studio. For more information, see Connect to SQL Server using Windows Authentication with the SQL adapter.

    For more information, see Security between the SQL Server and the adapter.

Security Best Practices for Consuming the SQL Adapter with BizTalk Server

  • The SQL adapter does not allow you to provide user name password credentials for the SQL Server database in the connection URI.

  • When you use the Consume Adapter Service Add-in, enter the user name password credential for the SQL Server database from the Security tab of the Configure Adapter dialog box.

  • When you configure the BizTalk WCF-Custom adapter for the SQL adapter on a send port, enter the user name password credential for the SQL Server database from the Credentials tab of the WCF-Custom Transport Properties dialog box.

  • When you configure the BizTalk WCF-Custom adapter for the SQL adapter on a receive location, enter the user name password credential for the SQL Server database from the Other tab of the WCF-Custom Transport Properties dialog box.

  • While using Consume Adapter Service Add-in to generate metadata, configuring send port, or configuring receive port, you can also use Windows Authentication. Before using Windows Authentication, you must add the Windows user as a user in SQL Server Management Studio. For more information, see Connect to SQL Server using Windows Authentication with the SQL adapter.

    For more information, see Security with the SQL adapter and BizTalk Server.

Security Best Practices for Consuming the SQL Adapter with Programming Solutions

  • It is sometimes necessary to provide the user name password credentials for the SQL Server database in the connection URI; however, if possible, you should avoid doing this.

  • When you use the Add Adapter Service Reference Visual Studio Plug-in, enter the user name password credential for the SQL Server database from the Security tab of the Configure Adapter dialog box.

  • In WCF channel model programming, use the Credentials property on the channel factory to set the user name password credential for the SQL Server database.

  • In WCF service model programming, use the ClientCredentials property on the WCF client to set the user name password credential for the SQL Server database.

  • If an application that consumes the SQL adapter sends messages that contain sensitive database information across a process boundary to another service or client, ensure that these messages have sufficient security measures applied to provide adequate data protection in your environment.

  • While using Add Adapter Service Reference Plug-in or connecting to SQL Server from a .NET application, you can also use Windows Authentication. Before using Windows Authentication, you must add the Windows user as a user in SQL Server Management Studio. For more information, see Connect to SQL Server using Windows Authentication with the SQL adapter.

    For more information, see Secure programming with the SQL adapter.

Security Best Practices for Hosting the SQL Adapter in IIS

Hosting the SQL adapter in Microsoft Internet Information Services (IIS) as a Web service exposes operations surfaced by the SQL adapter to Web clients. These operations might involve exchanging sensitive data over the Internet, so you should take measures to help ensure that this data is as secure as possible.

WCF provides two standard bindings for HTTP transport: the BasicHttpBinding provides basic HTTP transport with no security mechanisms; the WSHttpBinding supports both transport-level and message-level security mechanisms.

You can either use the BasicHttpBinding over an HTTPS connection, or use the WSHttpBinding to help protect your data. The WCF LOB Adapter SDK includes the WCF LOB Adapter Service Development Wizard to generate WCF service for LOB artifacts. This wizard only supports use of BasicHttpBinding.

You can also develop a custom HTTP binding to leverage additional security mechanisms that your environment provides. For more information about the security features that WCF provides, see Securing Services and Clients.

When hosting the SQL adapter as a Web service, Web developers should take measures to prevent strings typed in by users from being passed directly to the SQL Server database. For example, if a Web site lets the user enter a value that will be part of a WHERE clause in a SELECT statement, the input string should be scanned to prevent adding other commands to the statement.

Security Best Practices for WCF Diagnostic Tracing and Message Logging

WCF supports diagnostic tracing and message logging. You configure diagnostic tracing and message logging either through configuration files or by using Windows Management Instrumentation (WMI). Depending on the configuration options you set, WCF diagnostic tracing or message logging can emit sensitive information to log files, where it could potentially be exposed to observation by unauthorized users.

Follow the recommendations provided in the WCF documentation to mitigate potential security threats exposed by enabling these features. At a minimum, you should observe the following best practices for diagnostic tracing and message logging:

  • Do not enable “verbose” or “information” tracing in a production environment. This may lead to performance degradation. However, you must enable “warning” and “error” tracing in a production environment. If you enable tracing, you must take proper security measures to protect your data. See the WCF documentation for more information.

  • Ensure that log files and configuration files are protected by access control lists (ACLs).

    The following warnings apply specifically to the messages that are exchanged between a client application and the SQL adapter:

  • WCF diagnostic tracing can log the header (but not the body) of messages exchanged with the SQL adapter. Because the message action is in the message header, this reveals the operations invoked on the SQL adapter by the client.

  • If WCF message logging is enabled and logMessagesAtServiceLevel is true, the message header (but not the message body) of messages exchanged between the adapter client and the SQL adapter are logged. Because the message action is in the message header, this reveals the operations that the client invoked on the SQL adapter. If logEntireMessage is also true, the message body will be logged. This can reveal sensitive database information.

    For more information about improving security when you enable diagnostic tracing, see Security Concerns and Useful Tips for Tracing. For more information about improving security when you enable message logging, see Security Concerns for Message Logging.

See Also

Secure your SQL applications