Capturing cmdlet logging can be very helpful when diagnosing error messages or other unexpected behavior.
You will simply need to set two connection properties to begin capturing cmdlet logging.
- Logfile: A filepath which designates the name and location of the log file.
- Verbosity: This is a numerical value (1-5) that determines the amount of detail in the log. See the page in the Connection Properties section for an explanation of the five levels.
- MaxLogFileSize: When the limit is hit, a new log is created in the same folder with the date and time appended to the end. The default limit is 100 MB. Values lower than 100 kB will use 100 kB as the value instead.
- MaxLogFileCount: A string specifying the maximum file count of log files. When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted. Minimum supported value is 2. A value of 0 or a negative value indicates no limit on the count.
Once this property is set, the cmdlet will populate the log file as it carries out various tasks, such as when authentication is performed or queries are executed. If the specified file doesn't already exist, it will be created.
The verbosity level determines the amount of detail that the cmdlet reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are described in the following list:
|1||Setting Verbosity to 1 will log the query, the number of rows returned by it, the start of execution and the time taken, and any errors.|
|2||Setting Verbosity to 2 will log everything included in Verbosity 1, cache queries, and additional information about the request.|
|3||Setting Verbosity to 3 will additionally log HTTP headers, as well as the body of the request and the response.|
|4||Setting Verbosity to 4 will additionally log transport-level communication with the data source. This includes SSL negotiation.|
|5||Setting Verbosity to 5 will additionally log communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands.|
The Verbosity should not be set to greater than 1 for normal operation. Substantial amounts of data can be logged at higher verbosities, which can delay execution times.
To refine the logged content further by showing/hiding specific categories of information, see LogModules.
Sensitive DataVerbosity levels 3 and higher may capture information that you do not want shared outside of your organization. The following lists information of concern for each level:
- Verbosity 3: The full body of the request and the response, which includes all the data returned by the cmdlet
- Verbosity 4: SSL certificates
- Verbosity 5: Any extra transfer data not included at Verbosity 3, such as non human-readable binary transfer data
Best Practices for Data Security
Although we mask sensitive values, such as passwords, in the connection string and any request in the log, it is always best practice to review the logs for any sensitive information before sharing outside your organization.
You may want to refine the exact information that is recorded to the log file. This can be accomplished using the LogModules property.
This property allows you to filter the logging using a semicolon-separated list of logging modules.
All modules are four characters long. Please note that modules containing three letters have a required trailing blank space. The available modules are:
- EXEC: Query Execution. Includes execution messages for original SQL queries, parsed SQL queries, and normalized SQL queries. Query and page success/failure messages appear here as well.
- INFO: General Information. Includes the connection string, driver version (build number), and initial connection messages.
- HTTP: HTTP Protocol messages. Includes HTTP requests/responses (including POST messages), as well as Kerberos related messages.
- SSL : SSL certificate messages.
- OAUT: OAuth related failure/success messages.
- SQL : Includes SQL transactions, SQL bulk transfer messages, and SQL result set messages.
- META: Metadata cache and schema messages.
- TCP : Incoming and Ongoing raw bytes on TCP transport layer messages.
LogModules=INFO;EXEC;SSL ;SQL ;META;
Note that these modules refine the information as it is pulled after taking the Verbosity into account.