Guidewire, SAP, Oracle, UNIX, Genesys Technology Blog: MS SQL Server

Showing posts with label MS SQL Server. Show all posts

2021-06-17

MS SQL Server - Create OLE DB Connection File

Product: MS SQL Server
Version: All

I am surprise to find that there is not much information in the Internet to create an OLE DB connection file in Windows. Most of the information are creating OLE DB connection through software such as SSIS, Excel, DB utilities.

Following is the process to create OLE DB connection file, which can be used to specify the desire MS SQL Server OLE DB version:

Open Windows Explorer and navigate to the desire directory to create the OLE DB connection file
In Windows Explorer, right click on the empty area, and pick New > Text Document
Enter desire connection file name, such as "test1.udl"
Now you will see the icon for "test1.udl" as shown below
Open up the properties screen by doing Alt-Double-Click to see the pop up window below
Click on tab "Connection"
Enter the DB configuration values

Server name and port, separate by comma
Choose Windows integrated authentication or DB
For DB authentication, fill in username and password
Choose whether want to save password, if Windows Group Policy allow by your company's ActiveDirectory server
Select the database name to use after login

After configured, click on button "Test Connection" to confirm that the machine can login to remote MS SQL Server
You can further pick your desire OLE DB driver from the "Provider" tab

2020-03-30

MS SQL Server: Java DB Driver Tracing

Product: MS SQL Server with Java
Version: Any version

Microsoft docs.Microsoft.com homepage has a page regarding diagnosing problems with JDBC driver, but it is not comprehensive.

https://docs.microsoft.com/en-us/sql/connect/jdbc/tracing-driver-operation?view=sql-server-ver15

This post is going to provide additional information to supplement Microsoft's doc. I am going to use TLSv1.2 with MS SQL Server cluster (currently called AlwaysOn) as typically troubleshooting involves both.

Some critical information that Microsoft failed to state:

Every different OS vendor and bit could provide different output
Every different Java version could provide different output
Every different Java vendor's, e.g. Oracle Java, Open Java, IBM Java, SAP Java, will provide different output
Every different JDBC driver version could provide different output
Every different Java's lib/security/java.security could produce different output. To be technically correct, the cipher could be different
Every different Java's lib/security/local_policy.jar could produce different output
Every different Java's lib/security/US_export_policy.jar could produce different output
If MS SQL Server driver log is configured to output to console (via java.util.logging), then it can only capture INFO log level, but not FINE. This does not able to help in troubleshooting. It must output to log file
MS SQL Server certificate must be created as per requirement (intermediate SSL cert doesn't need to follow this requirement)

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15

When troubleshooting JDBC connectivity issue, always ask for:

OS version, and bit, e.g. Windows 7 64-bit
"java -version" output to confirm the vendor, and version
A copy of the JDBC driver file to simulate across diff env, including reproduce locally
Check JDBC driver version, if don't have the JAR file
Copy of file jre/lib/security/java.security
Copy of file jre/lib/security/local_policy.jar
Copy of file jre/lib/security/US_export_policy.jar
Copy of file jre/lib/security/cacerts, which is the Java keystore
Copy of SSL certificate of MS SQL Server
Copy of intermediate SSL certificate of MS SQL Server

Location of logging.properties File

Depending on the Java application, or J2EE application that you are troubleshooting the issue, you might be able to modify the Java option to specify different location of the file, or not. This section going to show various options to keep the logging.properties file

Create logging.properties in jre/lib/java.util.logging - use this if you cannot specify alternate location of java.util.logging
Create file in current directory, and specify Java option -Djava.util.logging.config.file=logging.properties
Create file in specific directory, e.g. D:\temp\, and specify Java option -Djava.util.logging.config.file=D:\temp\logging.properties

Example 1: Run Java program with logging.properties in D:\temp\logging.properties with -jar option

java -Djava.util.logging.config.file=D:\temp\logging.properties -jar [java_application].jar [class name]

2019-08-01

MS SQL Server: Easy to Move DB Files

Product: MS SQL Server
Version: 2000 - 2017

Used following steps to move MS SQL Server data files, except master database

1. Generate SQL to specify the new file location, where new file location is D:\SQLServer2017\Data

SELECT name, physical_name , state_desc , 'ALTER DATABASE ' + db_name(database_id) + ' MODIFY FILE ( NAME=' + name + ', FILENAME = ''D:\SQLServer2017\Data\'+
substring(physical_name, 72,30) + ''')' Generated_Stmt
FROM sys.master_files
where database_id <> DB_ID('master')

2. Run the SQL generated from column "Generated_Stmt" such as

ALTER DATABASE tempdb MODIFY FILE ( NAME=tempdev, FILENAME = 'D:\SQLServer2017\Data\')
ALTER DATABASE tempdb MODIFY FILE ( NAME=templog, FILENAME = 'D:\SQLServer2017\Data\')
ALTER DATABASE model MODIFY FILE ( NAME=modeldev, FILENAME = 'D:\SQLServer2017\Data\')
ALTER DATABASE model MODIFY FILE ( NAME=modellog, FILENAME = 'D:\SQLServer2017\Data\')
ALTER DATABASE msdb MODIFY FILE ( NAME=MSDBData, FILENAME = 'D:\SQLServer2017\Data\')
ALTER DATABASE msdb MODIFY FILE ( NAME=MSDBLog, FILENAME = 'D:\SQLServer2017\Data\')

3. Restart MS SQL Server daemon from Windows Services

2018-06-13

MS SQL Server 2012: Step-by-step to Enable TLSv1.2 in Java 1.7

MS SQL Server 2012: Step-by-step to Enable TLSv1.2

Introduction

I assume this is not the first article you stumble in how to enable TLSv1.2 in Java 1.7.x for MS SQL Server 2012. Java 1.8 or later is able to communicate in TLS v1.2 protocol with MS SQL Server, but not for Java 1.7 u80 and earlier.

Enabling TLS v1.2 implies you are going to enable database communication encryption, e.g. used for the login (this is in plain English, and it does not enable database level encryption which is for each DB).

This article is mainly covering audience who are using JDBC application, such as J2EE. Application that used .NET, ODBC, or Windows base application will require patching of those framework and driver, which will be lengthy to specify.

The instruction I provided is going to be on a new MS Windows 2012R2 Server OS. Following are the tasks that I am going to cover, which is needed:

Patching MS Windows Server 2012 R2 OS - any edition
Creating SSL self-sign digital certificate - openssl is used to generate the cert
Configures database communication encryption
Disabling protocol TLS 1.0 in Windows OS - this is the default protocol used by MS SQL Server 2012 if you don't disable it
Disabling protocol TLS 1.1 in Windows OS
Using latest MS SQL Server 2012 JDBC driver
Testing

Patching MS Windows Server 2012 R2 OS

Only Windows Server 2016 pre-configured with TLS v1.2 with the rest disabled. So the first thing you need to do is to enable Windows Update, and let it install the all the patches, and reboot several rounds if needed. The MS documentation for this OS is not well maintain and several iteration of patches released on SSL 3.0, TLS 1.0 and TLS 1.1. If you like, you can spend your own time to research all related articles and sort them in proper order. Various piece and inter-related patch is wasting my valuable time to test them out to confirm their effective outcome, so I am going to skip those specific patch.

If you skipped this step, then you might encounter difficulty to in SQL Server Configuration Manager where SSL certificate not showing.

In short, update the OS with all the patches.

Creating SSL self-sign digital certificate

There is no much updated documentation in the Internet about creating self-sign digital certificate using openssl for MS SQL Server. Most of them are using openssl to create a certificate for IIS, which are missing some standard that MS SQL Server Configuration Manager will check. If you created the certificate randomly, then Configuration Manager won't display the SSL certificate, and you won't be able to activate it. In other word, the next step will not be possible

Creates following openssl configuration file, and give it any name that you like. In this example, I am going to called it cert_config.txt, as my editor will open it faster if I used txt extension

cert_config.txt

#cert_config.txt

distinguished_name = req_distinguished_name
x509_extensions = v3_req
prompt = no

[req_distinguished_name]
C = CA
ST = ON
L = Toronto
O = Blogger
OU = SingCheong
CN = win2012-sql2012sp3-1

[v3_req]
keyUsage = keyEncipherment, dataEncipherment
extendedKeyUsage = serverAuth
subjectAltName = @alt_names
# PKIX recommendations harmless if included in all certificates.
subjectKeyIdentifier=hash

[alt_names]
DNS.1 = win2012-sql2012sp3-1.blogger.com
DNS.2 = win2012-sql2012sp3-1

This step is critical and all the above configuration must be exactly as it is. Following is the highlight:

CN entry must be the hostname of MS SQL Server
DNS.1 and DNS.2 must contain the hostname of MS SQL Server
keyUsage must contain keyEncipherment
extendedKeyUsage must contain serverAuth so that MS SQL Server can encrypt the communication
subjectKeyIdentifier must exists in the X509 extension. I don't hard coded it, which is possible, but I let it auto generate using "hash." Above 4 is specified in MS doc about TLS v1.2, except this one

Again, if you missed anything of the 5 listed above, the SSL certificate won't show up in SQL Configuration Manager

A lot of the homepage are using Windows build-in certificate request method to generate proper certificate or self-sign certificate. This will require some setup in Active Directory, or pays CA (certificate authority) money to buy one. As a hacker, I prefer to use free utility to generate the certificate so that I have better understanding of the content of the certificate, as well as how to verify everything I created

Download openssl 1.0.2 or 0.9.8. I used 0.9.8 which already installed in my old computer, but I recommend 1.0.2 for future proof. Any version that recognize above openssl syntax will do.

1. Creates the root certificate where the MS SQL Server cert will trust. It is going to point to default openssl.cnf that comes with OpenSSL. The key will use RSA 4096-bit and SHA256 for modern encryption, valid for 1 yr. Generated cert file is called cert.pem, with key file as key.pem. You can enter anything in the C, ST, L, O, OU, CN, as this certificate is just going to be trusted by the next certificate (used in MS SQL Server), and this one is not going to be used in MS SQL Server

openssl req -new -newkey rsa:4096 -x509 -sha256 -days 365 -nodes -out cert.pem -keyout key.pem -config D:\Utilities\OpenSSL\share\openssl.cnf

2. Created the SSL certificate that is going to be used by MS SQL Server. Valid for 1 yr, cert file is gfcert.pem, key file is selfsigned.key

openssl req -x509 -sha256 -nodes -newkey rsa:4096 -keyout selfsigned.key -out gfcert.pem -days 365 -config cert_config.txt

3. Convert it to PFX file, and includes the SSL cert created in step #1. This is the certificate file importing into MS SQL Server's Windows OS. Do not import the PEM file above, even it will be accepted

openssl pkcs12 -export -out gfcert.pfx -in gfcert.pem -inkey selfsigned.key -certfile cert.pem

4. Copy file gfcert.pfx to Windows OS where MS SQL Server 2012 resides

You can verify the gfcert.pem now to ensure you have following required fields

Command:

openssl x509 -text -noout -in gfcert.pem

Output

D:\temp\SSL>openssl x509 -text -noout -in gfcert6.pem
WARNING: can't open config file: /usr/local/ssl/openssl.cnf
Certificate:
Data:
Version: 3 (0x2)
Serial Number:
b2:db:7f:8f:4a:58:ca:55
Signature Algorithm: sha256WithRSAEncryption
Issuer: C=CA, ST=ON, L=Toronto, O=Blogger, OU=SingCheong, CN=win2012-sql
2012sp3-1
Validity
Not Before: Jun 12 17:48:35 2018 GMT
Not After : Jun 12 17:48:35 2019 GMT
Subject: C=CA, ST=ON, L=Toronto, O=Blogger, OU=SingCheong, CN=win2012-sq
l2012sp3-1
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
Public-Key: (4096 bit)
Modulus:
00:b8:94:c5:91:5b:30:cf:d7:ce:58:82:7e:e0:cf:
49:23:79:23:18:97:93:e8:87:3b:9d:81:a4:47:b5:
cd:6d:2b:3a:50:08:7e:0b:64:e3:6a:15:8e:a9:98:
9e:e7:87:e6:52:0e:2e:82:b6:15:2e:07:c6:28:85:
08:01:81:33:fe:b2:f0:ae:62:d5:c0:ac:62:48:ac:
77:99:a5:33:a0:65:ef:3b:75:6a:94:06:a3:1b:ea:
5a:ad:b5:e7:4b:6f:b0:42:5f:2e:f3:d1:64:3a:a8:
d6:5b:23:11:51:09:13:eb:f9:72:84:86:a7:18:f8:
f8:7e:8f:d0:cc:24:ff:ee:d1:1c:80:ab:eb:31:87:
d1:37:0a:8f:37:31:51:57:12:64:5b:65:1f:84:d5:
26:b0:a7:1a:6d:82:1e:71:20:f9:07:90:52:d7:bc:
c9:15:1a:2c:08:e6:41:a3:21:83:28:8f:f5:49:19:
fd:de:1d:0d:54:60:5d:70:11:12:da:b0:01:5d:b7:
32:e6:a6:20:ab:39:68:11:94:8a:d0:9f:f9:d9:7b:
6e:a3:50:66:e5:75:7b:ab:90:73:8f:93:ee:8d:e1:
f8:62:de:b4:79:6b:db:03:c2:e6:fe:76:df:43:d5:
1b:b3:4a:d3:ba:e5:65:12:c6:86:a7:2b:b0:d2:ba:
9a:94:d8:ea:14:b9:74:e8:c0:d0:fa:ee:05:e1:61:
80:c8:96:04:a3:53:70:b0:7f:11:46:4c:35:9f:13:
37:2d:03:1d:6a:c4:15:e2:14:df:01:46:2b:ed:e9:
de:c2:9a:01:38:7a:fd:ab:e6:ac:7e:59:32:51:54:
29:19:0f:55:e5:c5:19:14:f8:7d:9d:0f:0a:f8:5f:
27:3d:fc:33:66:ee:b2:4d:90:d5:61:ea:d0:3e:db:
7f:00:2a:65:7b:6b:4f:45:ec:03:18:c2:60:2e:2e:
56:82:ab:67:45:7f:82:53:0b:fd:d7:1c:00:d9:79:
31:4d:c5:02:44:ce:4a:05:39:83:19:2b:1c:58:ed:
4f:ff:40:6f:84:b6:12:e2:4a:86:ad:7a:34:f3:49:
9f:2c:56:a5:cd:e3:e2:d1:1b:17:72:cf:85:0f:08:
82:81:f6:61:0b:65:f1:d1:a4:52:06:3a:b7:85:7a:
c1:8a:c5:cf:7e:b8:f1:e8:2e:8e:75:d9:25:4d:59:
80:95:a4:85:e1:9f:0c:71:6d:64:62:81:cb:c9:60:
8f:82:05:6a:3e:aa:95:3d:2b:b5:72:a4:a6:de:c7:
0a:6e:35:08:ac:d4:f8:73:ce:03:c6:d9:73:ef:af:
b3:36:f9:73:84:42:db:f8:8d:e5:75:2e:39:21:7b:
c0:21:fb

Exponent: 65537 (0x10001)
X509v3 extensions:
X509v3 Key Usage:
Key Encipherment, Data Encipherment
X509v3 Extended Key Usage:
TLS Web Server Authentication
X509v3 Subject Alternative Name:
DNS:win2012-sql2012sp3-1.blogger.com, DNS:win2012-sql2012sp3-1
X509v3 Subject Key Identifier:
50:AC:70:C2:F0:AE:C9:82:6B:D5:3E:4E:5F:F5:C1:D8:BF:6B:90:D0
Signature Algorithm: sha256WithRSAEncryption
78:0f:81:ee:40:0a:40:e9:52:6e:5c:89:6a:ab:99:be:f4:d3:
d2:3d:4e:dc:79:0f:af:3a:1a:80:92:88:53:ec:8e:49:aa:f7:
0c:45:8a:7b:e0:2b:14:03:63:fe:81:16:09:f7:70:38:59:7d:
4a:b8:bc:af:aa:e3:d8:82:ca:7e:d8:16:64:21:33:79:e8:87:
11:a5:4c:74:b5:33:95:c5:d4:4d:bb:6e:83:90:bc:1b:44:76:
06:14:8b:01:7a:34:19:0a:46:91:38:cb:e3:e6:68:dd:ec:cc:
e8:34:46:8a:dc:82:47:bb:31:48:6b:65:e3:ae:59:72:ee:2f:
9d:95:d4:c2:b9:0e:9d:e2:9c:96:ce:f4:f5:e3:d6:7a:7c:b4:
b6:07:a1:17:fa:bf:3f:80:5c:cf:32:1e:be:8e:6a:d5:fc:9e:
dd:78:90:67:7e:27:54:7c:8e:55:92:36:6d:30:79:f2:63:8f:
24:3a:ee:44:2e:ae:17:07:79:89:5c:75:40:c4:a2:22:46:02:
d7:ff:d3:93:6c:92:04:6b:1c:04:6a:c9:4d:59:e6:44:bf:7d:
06:7f:49:8d:f9:a2:04:47:b0:b2:9b:0e:91:9b:c1:3f:82:85:
0d:bd:76:13:02:6d:68:fe:3f:ce:b5:a9:f2:d9:e5:6a:c9:21:
3f:22:9d:49:d0:49:7f:61:db:52:ca:d5:dc:61:6f:f0:1a:21:
ef:16:a6:12:25:aa:25:a9:b4:70:93:95:b7:bd:e9:a5:54:62:
4e:12:bb:e2:d4:21:80:e3:97:1a:60:fa:ad:cc:03:26:64:1a:
b8:56:0e:86:4d:ed:0d:95:be:3c:6b:0f:cc:cc:9a:51:81:2d:
72:b7:8a:1b:2f:12:cc:4c:75:d9:52:a5:f7:d2:75:78:78:3d:
c4:89:3b:77:a1:79:6a:06:26:8e:66:ef:e4:94:10:62:ec:7a:
89:2f:d2:39:86:f9:94:c9:1e:61:3e:bf:16:88:a7:e5:b2:2f:
7f:da:38:dd:a9:37:83:14:4b:47:ef:63:04:88:37:b9:c9:f4:
5c:3f:f2:1e:da:84:84:6b:6b:36:d6:2b:af:11:98:a2:ec:9f:
8e:76:69:bc:b4:fa:58:04:af:a5:d8:dd:8c:b9:bd:8e:d4:65:
60:27:56:82:9d:32:01:86:c9:68:90:6f:da:be:c8:46:d5:db:
ec:00:b2:fd:9b:ed:5f:d8:f6:3a:27:a9:c5:54:c1:ef:d4:27:
58:c2:fa:86:35:16:6d:9a:9b:a2:3d:a7:a2:4f:67:fa:23:cf:
fb:9d:fe:a1:6a:60:ab:ef:81:e9:48:a2:ad:66:8c:58:57:ea:
f2:07:1f:c9:af:43:4c:56

Verify #1: Certificate is valid

Validity

Not Before: Jun 9 03:07:02 2018 GMT

Not After : Jun 10 03:07:02 2020 GMT

Verify #2: Subject line contain hostname in CN

Subject: C=CA, ST=ON, L=Toronto, O=Blogger, OU=SingCheong, CN=sup-sql-2012sp3

Verify #3: DNS line contain hostname

X509v3 Subject Alternative Name:

DNS:win2012-sql2012sp3-1.blogger.com, DNS:win2012-sql2012sp3-1

Verify #4: keyEncipherment in X.509

Look into X.509 v3 extension to find keyEncipherment

X509v3 extensions:

X509v3 Key Usage:

Key Encipherment, Data Encipherment

Verify #5: serverAuth in X.509

Look into X.509 v3 extension to find serverAuth

X509v3 extensions:

X509v3 Extended Key Usage:

TLS Web Server Authentication

Verify #6: subjectKeyIdentifier in X.509

Look into X.509 v3 extension to find serverAuth subjectKeyIdentifier

X509v3 extensions:

X509v3 Subject Key Identifier:

50:AC:70:C2:F0:AE:C9:82:6B:D5:3E:4E:5F:F5:C1:D8:BF:6B:90:D0

Windows OS doesn't need reboot

Import Self-sign Certificate to Personal Certificate Store

This section is taken from MS homepage below

https://technet.microsoft.com/en-ca/library/cc995171.aspx

Press Windows-R, and type mmc
Press Ctrl-M, and drag "Certificates" into right panel. In the 1st prompt, choose "Computer account"
In 2nd prompt that said "Select computer," choose "Local computer"
Close it dialog by clicking OK button, and you will back to MMC screen
Expand root "Certificates" folder, and you will see "Personal" as the 1st folder
If you never import any certificate before, you won't see child "Certificates" folder. This is where many internet forum giving misleading info that it will be visible
Right-click "Personal" > All tasks > Import... to open up the dialog to import the pfx file
Dialog box "Certificate Import Wizard" will show up, and you can fill in the gfcert.pfx file
Remember to change the file filter to PKCS#7 so that the pfx file will show up, otherwise it will only show file with .cer extension
Windows OS doesn't need reboot

Configures database communication encryption

This is the most crucial part of SSL/TLS enabling step. If the SSL certificate is not created properly, then you will not able to pick the certificate created in previous step.

I will just show the screenshots. Perform this step in MS SQL Server

If you created the certificate correctly as specified above, then the SSL certificate will be able to list below. I did not manually update Windows registry to add the thumbprint of the certificate like some other homepage indicated, although I am using older Windows 2012 R2

Now the MS SQL Server can accept both encrypted and un-encrypted connection. In other word, the client will specify whether to use SSL/TLSv1.2 encryption. If you only want to enable encrypted SSL/TLSv1.2, then you can enforce it at server level.

Let me give you 2 JDBC URL and their behavior

Following JDBC URL will try non-encrypted connection first, before encrypted, if server doesn't enforce:
jdbc:sqlserver://192.168.1.2:2012;DatabaseName=cc807_sc;User=dbadmin1;Password=xx

Following JDBC URL will try encrypted connection only, even if server doesn't enforce. Since our SSL certificate is self-signed, so we need to specify trustServerCertificate:
jdbc:sqlserver://192.168.1.2:2012;DatabaseName=cc807_sc;User=dbadmin1;Password=xx;encrypt=true;trustServerCertificate=true

Following JDBC URL will try encrypted connection with TLS v1.2 protocol first, as well as trust the self-signed SSL certificate:
jdbc:sqlserver://192.168.1.2:2012;DatabaseName=cc807_sc;User=dbadmin1;Password=xx;sslProtocol=TLSv1.2;trustServerCertificate=true

Restart SQL Server Windows service to activate SSL/TLS v1.2 encryption. Now it will accept following connection:

Un-encrypted connection
Encrypted connection - SSL/TLS v1.0 - This is the default for MS SQL Server 2012 with JVM <= 1.7u80 (latest free download from Oracle. Version higher only available to paid Oracle customer)
Encrypted connection - SSL/TLS v1.1 - None of the JVM will use this encryption protocol
Encrypted connection - SSL/TLS v1.2 - JVM 1.7u90 and higher or 1.8 and higher
Encrypted connection - SSL/SSL v3.0 - JVM 1.7u80 and older will fall back to this protocol once it failed TLS v1.0. Once you patched your client OS and Java, both will typically deny this protocol

Install Network Sniffer Utility

In this TLS v.1.2 step, I do not explicitly disable SSL 3.0, TLS 1.0, and TLS 1.1 in the OS hosting MS SQL Server instance (yes, if you still confuse, the way to disable SSL3-TLS1.1 is in OS level, not DB). In other word, MS SQL Server 2012 will accept all 5 types of connections below:

Un-encrypted connection
Encrypted connection - SSL/TLS v1.0
Encrypted connection - SSL/TLS v1.1
Encrypted connection - SSL/TLS v1.2
Encrypted connection - SSL/SSL v3.0

The main reason I do that is because it is very hard to troubleshoot these 5 encryption handshaking from client end (even it is a J2EE server application, it is consider a DB client). No J2EE, Servlet, applet, or Java application will display a graceful encryption handshake error. Therefore, I will leave the server to accept both encrypted and unencrypted communication, then disables it after configured the DB application to use TLS v1.2.

There is no utility that can determine from MS SQL Server end that the encryption protocol is TLS v1.2 or older. Network packet sniffer is the only utility to confirm that. The network sniffer utility I chosen is an old MS Network Manager utility because of following reasons:

NM34_x64.exe installer is 6.52MB only
netmon.exe executable used 55MB RAM, so the rest of the RAM can be used to hold the ntework packets
Free

Download the software from Microsoft homepage below:
https://www.microsoft.com/en-ca/download/details.aspx?id=4865

If you prefer the new replacement utility from Microsoft, it is called Microsoft Message Analyzer, and can be found in following Microsoft homepage:
https://www.microsoft.com/en-ca/download/details.aspx?id=44226

1. Run the utility and click on "New Capture"

2. Click on "Capture Setting" to filter capturing from J2EE server, and port 2012 (or 1433 default)

Fill in "Current capture filter" as shown in above screenshot, i.e.
IPv4.Address == 192.168.1.2
and tcp.Port== 2012

3. Optionally, you can fill in the "Display filter" if you are capturing multiple J2EE server IP, but use display filter to control the output on the screen

4. You can run the J2EE application, or Java applet/application/servlet/Tomcat/WebLogin/WebSphere/Cognos etc while capturing it. Following is what you should pay attention and able to tell which SSL protocol is used. In this example for Java 1.7u80, it is using SSL/TLS v1.0

The network frame detail will tell you whether it is SSL/SSL v3.0, TLS v1.0, v1.1, v1.2.

Following output is SSL/TLS v1.0:

TLS: TLS Rec Layer-1 SSL Application Data
TlsRecordLayer: TLS Rec Layer-1 SSL Application Data
ContentType: SSL Application Data
Version: TLS 1.0

Following output is SSL/TLS v1.2:

TLS: TLS Rec Layer-1 SSL Application Data
  TlsRecordLayer: TLS Rec Layer-1 SSL Application Data
  ContentType: SSL Application Data
  Version: TLS 1.2

Testing MS SQL Server 2012 Using openssl

Following openssl command cannot be used to test SSLv3.0 - TLSv1.2 protocol reliably

openssl s_client -connect db_server:1433 -tls1_2

openssl s_client -connect db_server:1433 -tls1_1

openssl s_client -connect db_server:1433 -tls1

openssl s_client -connect db_server:1433 -ssl3

MS SQL Server SSL handshake works different way than https, smtps, ftps. The MS SQL Server will always accept the openssl connection, but it won't display the SSL certificate, cipher, or will hang after shown it is connected

Sample output that hang:

D:\>openssl s_client -connect db_server:1433 -tls1_1

WARNING: can't open config file: /usr/local/ssl/openssl.cnf

CONNECTED(000001C0)

Sample output on MS SQL Server where TLS v1.2 is configured, as well as not configured. They output are identical

D:\>openssl s_client -connect db_server:1433 -tls1_2

WARNING: can't open config file: /usr/local/ssl/openssl.cnf

CONNECTED(000001C0)

9240:error:1409E0E5:SSL routines:ssl3_write_bytes:ssl handshake failure:.\ssl\s3_pkt.c:659:

---

no peer certificate available

---

No client certificate CA names sent

---

SSL handshake has read 0 bytes and written 0 bytes

---

New, (NONE), Cipher is (NONE)

Secure Renegotiation IS NOT supported

Compression: NONE

Expansion: NONE

No ALPN negotiated

SSL-Session:

Protocol : TLSv1.1

Cipher : 0000

Session-ID:

Session-ID-ctx:

Master-Key:

Key-Arg : None

PSK identity: None

PSK identity hint: None

SRP username: None

Start Time: 1529075233

Timeout : 7200 (sec)

Verify return code: 0 (ok)

---

Configuring Java J2EE Application to Enable TLS v1.2

Most Java applications, e.g.Java servlet, Apache Tomcat J2EE, Java application, WebSphere EAR, WebLogic EAR, JBoss WAR, etc are having a configurable JDBC URL, and Java runtime parameters.

The JDBC URL can be easily configured to establish SSL/TLS v1.2 protocol

Ref 1: https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-2017
Ref 2: https://github.com/Microsoft/mssql-jdbc/wiki/SSLProtocol

Microsoft has very poor documentation for their older database in the JDBC URL connection string. Above 2 links are only showing for their MS SQL Server 2017, while another link doesn't even mentioned which version it is applicable for.

The JDBC URL that should enforce TLSv1.2 protocol for MS SQL Server driver is below:

sslProtocol - Introduced in JDBC Driver v6.4. Supported value are TLS, TLSv1, TLSv1.1, TLSv1.2

E.g.

jdbc:sqlserver://db_server:1433;sslProtocol=TLSv1.2;trustServerCertificate=true

jdbc:sqlserver://db_server:1433;sslProtocol=TLSv1.1;trustServerCertificate=true

jdbc:sqlserver://db_server:1433;sslProtocol=TLSv1;trustServerCertificate=true

Ref: https://docs.microsoft.com/en-us/sql/connect/jdbc/release-notes-for-the-jdbc-driver?view=sql-server-2017

I have not able to make my sample Java application to use TLS v1.2 in JVM 1.7u80 (latest free Oracle Java from download.oracle.com).

In term of troubleshooting, you can add Java command line parameter like below so that the console will display SSL low level debug messages. If it is Tomcat server, then it will be in catalina.log

$JAVA17_HOME/bin/java -Djavax.net.debug=ssl -cp .;mssql-jdbc-6.4.0.jre8.jar JDBCVersion2

Note 1: -Djdk.tls.client.protocols=TLSv1.2 is not applicable for DB troubleshooting, but only used for web/SOAP https communication
Note 2: -cp is used to specify the MS SQL Server JDBC driver, so that you can quickly switch among many different drivers. This is useful is the driver is provided by 3rd party, or you are testing your program's compatibility with various driver version, or Java version, or both

Unsupported Java Feature in 1.7

This section is specially listing down Java feature that available in 1.8, but not for 1.7. There are many partial information in the Internet, and I specifically created this section to address all.

-Djdk.tls.disabledAlgorithms =SSLv3,TLSv1,TLSv1.1	This is not available for 1.7, but valid for 1.8 and later Even for 1.8, this only affect https and not for MS SQL Server
-Dhttps.protocols="SSLv3,TLSv1.0,TLSv1.1"	This only affects https outbound, and not application to MS SQL Server as it doesn't talk in https
-Djdk.tls.client.protocols="TLSv1.2,TLSv1.1"	This is not available for 1.7u94 and earlier, but valid for 1.7u95 (paid) & 1.8 and later It is able to control the SSL/TLS protocol used

Reference:
https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-R2-and-2012/dn786418(v=ws.11)
https://docs.microsoft.com/en-us/sql/connect/jdbc/release-notes-for-the-jdbc-driver?view=sql-server-2017

2017-10-20

MS SQL Server: Verify Login Timeout

Product: MS SQL Server
Version: 2000 - 2016

Microsoft MS SQL Server pre-install with login timeout of 10sec. Many developers or support personnel might have a chance to encounter login timeout while DBA might tell you that there is no login timeout, as they could be confused with other timeout values

There are 3 different timeout setting in MS SQL Server, so there is no surprise that a junior DBA mistaken one for other.

In the Internet, there is not much info about how to verify these 3 timeout setting, including login timeout. This blog post is specifically to cover it.

How to Verify Using SELECT

Uses following SELECT statement from any MS SQL Server client, such as Toad, DbVisualizer, SSMS, as well as programming. This approach is not widely documented especially on verifying login timeout

select configuration_id, name, value_in_use from sys.configurations where configuration_id in (1519, 1541, 1520)

Sample output:

configuration_id	name	value_in_use
1519	remote login timeout (s)	10
1520	remote query timeout (s)	600
1541	query wait (s)	-1

Note: Above is default values for new MS SQL Server installation

You can see clearly that "remote login timeout" is 10 sec for default MS SQL Server installation. DBA often confused that with "query wait" which is never, or no timeout.

Pros:

It is a regular SELECT statement that be easily written in any programming language
It is easily executes in web application
SELECT statement is 100% supported by any DB client, and utilities, e.g. Toad, Eclipse, Excel, DbVisualizer
Does not require to engage DBA to verify it
Execute once to see all 3 timeout values without hoping into various screens

Cons:

People who don't understand SELECT statement syntax might find it a lots of keys to enter
Not GUI base
Requires to run in SSMS or DB client
Junior DBA less familiar with this output, and might has more push back

How to Verify Using sp_configure

sp_configure statement can be used to both verify as well as set the database instance value.

Following statement can be used to verify 3 different timeout parameters. You need to execute it 3 times as sp_configure command only allow to display 1 value

sp_configure 'query wait (s)'
go
sp_configure 'remote query timeout (s)'

sp_configure 'remote login timeout (s)'

go

Sample output:

Pros:

Simpler syntax compare to SELECT statement
Less typing
Junior DBA more familiar with this output
Statement is 100% supported by any DB client, and utilities, e.g. Toad, Eclipse, Excel, DbVisualizer
Does not require to engage DBA to verify it
Minor modification to the command will allow DBA to set the new value in real time

Cons:

Cannot obtain all 3 values in 1 statement
More effort in programming to integrate it, as well as get the 3 parameters
Not GUI base
Requires to run in SSMS or DB client

How to Verify Using GUI

This is the most commonly documented in MSDN or Technet. All junior DBA are familiar with this approach, as there is no learning curve

Launch SSMS, open up the DB instance properties to verify these 3 timeout values

Pros:

GUI driven, and no need to memorize any statement or syntax
No typing requires
Junior DBA very familiar with this screen, and no push back
Quick & easy clicking on 2 screens to see all 3 timeout values
No need to memorize any command, such as SELECT and sp_configure

Cons:

Requires SSMS
Not supported by any DB client
Requires some skill to map the values in GUI to the sp_configure values
Impossible to written in any programming language
Impossible to execute in web application
Might need to engage DBA if firewall blocks SSMS
Needs to check 2 screens to determine all 3 parameters

2017-01-04

MS SQL Server: Finding JDBC Version

Product: MS SQL Server
Version: 2008R2 - 2014

Every Microsoft JDBC JAR version bundled with 4 sqljdbc.jar files for 4 different versions of major Java JVM. When you download the driver from Microsoft, it will include 4 JAR files, and you need to pick the proper JAR file according to your JVM version.

For example, following Microsoft JDBC 4.2 bundled with sqljdbc42.jar that needs JVM 1.8, sqljdbc41.jar that needs JVM 1.7, sqljdbc4.jar that needs JVM 1.6, and sqljdbc.jar that needs JVM 1.5.

https://msdn.microsoft.com/en-us/library/ms378422(v=sql.110).aspx

From this MSND, you can see that JDBC v4.2 has another versioning called JDBC Version Compliance for each file.

So when you received a file called sqljdbc.jar, it is hard to tell what version of Microsoft JDBC driver version it belongs to, but only able to tell it support JVM 1.5 or older.

Therefore, I developed following Java application that can display both the JDBC version, as well as JDBC Version Compliance

Sample Output

D:\temp\sqljdbc>C:\Language\jdk1.8.0_92_64\bin\java -cp sqljdbc42.jar;. JDBCVersion
JDBC Driver version: 4.2
Driver name: Microsoft JDBC Driver 6.0 for SQL Server
Driver version: 6.0.7728.100
Product name: Microsoft SQL Server
Product version: 11.00.5058

D:\temp\sqljdbc>C:\Language\jdk1.8.0_92_64\bin\java -cp sqljdbc41.jar;. JDBCVersion
JDBC Driver version: 4.1
Driver name: Microsoft JDBC Driver 6.0 for SQL Server
Driver version: 6.0.7728.100
Product name: Microsoft SQL Server
Product version: 11.00.5058

D:\temp\sqljdbc>C:\Language\jdk1.8.0_92_64\bin\java -cp sqljdbc4.jar;. JDBCVersion
JDBC Driver version: 4.0
Driver name: Microsoft JDBC Driver 4.0 for SQL Server
Driver version: 4.0.4621.201
Product name: Microsoft SQL Server
Product version: 11.00.5058

D:\temp\sqljdbc>java -cp C:\ClaimCenter7.0.7\lib\sqljdbc4.jar;. JDBCVersion
JDBC Driver version: 4.0
Driver name: Microsoft SQL Server JDBC Driver 3.0
Driver version: 3.0.1301.101
Product name: Microsoft SQL Server
Product version: 11.00.5058

D:\temp\sqljdbc>java -cp C:\ClaimCenter9.0.0\admin\lib\sqljdbc-4.1.jar;. JDBCVersion
JDBC Driver version: 4.0
Driver name: Microsoft JDBC Driver 4.1 for SQL Server
Driver version: 4.1.5605.100
Product name: Microsoft SQL Server
Product version: 11.00.5058

Source code

Filename: JDBCVersion.java
import java.sql.*;

class JDBCVersion
{
public static void main(String[] args) {

Connection conn = null;

try {

String dbURL = "jdbc:sqlserver://localhost\\SQLSERVER2012";
String user = "sa";
String pass = "sa";
conn = DriverManager.getConnection(dbURL, user, pass);
if (conn != null) {
DatabaseMetaData dm = (DatabaseMetaData) conn.getMetaData();
System.out.println("JDBC Driver version: " + dm.getJDBCMajorVersion() + "." + dm.getJDBCMinorVersion());
System.out.println("Driver name: " + dm.getDriverName());
System.out.println("Driver version: " + dm.getDriverVersion());
System.out.println("Product name: " + dm.getDatabaseProductName());
System.out.println("Product version: " + dm.getDatabaseProductVersion());

}

} catch (SQLException ex) {
ex.printStackTrace();
} finally {
try {
if (conn != null && !conn.isClosed()) {
conn.close();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}

Compiling Instruction

C:\Language\jdk1.8.0_92_64\bin\javac JDBCVersion.java

This will create JDBCVersion.class in current directory

Execution Instruction

1. Download MS SQL Server JDBC driver to C:\temp
2. Extracts all the JAR files into C:\temp\sqljdbc. In typical usage, you will want to copy the JAR file that you would like to find out its version
3. Run and specify the JDBC jar files that you want to verify the version

C:\Language\jdk1.8.0_92_64\bin\java -cp sqljdbc4.jar;. JDBCVersion

where you have changed the current directory to where JDBCVersion.class resides

2016-12-06

MS SQL Server: bcp import unicode file

Product: MS SQL Server
Version: 2005 - 2016

Overview

MS SQL Server bcp command line utility and BULK IMPORT statement(T-SQL) do not provide any way to do debugging, as well as previewing. This becomes frustrating when you are using fmt (format file) as well as throwing additional complication by using Unicode text file.

This post going to focus in bcp command line utility, but the information is applicable to BULK IMPORT statement.

bcp input (import) will display very generic, and useless error message when there the fmt file is incorrectly configured to match the data file's content. For example:

Error #1

Starting copy...

SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]I/O error while reading BCP format file

BCP copy in failed

Error #2:

Starting copy...

SQLState = S1000, NativeError = 0

Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

BCP copy in failed