| Both sides previous revisionPrevious revisionNext revision | Previous revision |
| sql_server_express_connnection_tutorial [2024/01/26 11:18] – [Troubleshooting] amyw | sql_server_express_connnection_tutorial [2024/01/26 12:03] (current) – [Connect to SQL Server Express database with JDBC Driver in MIStudio] amyw |
|---|
| database class name: com.microsoft.sqlserver.jdbc.SQLServerDrive | database class name: com.microsoft.sqlserver.jdbc.SQLServerDrive |
| database url: jdbc:sqlserver://hostname:1433;databaseName=yourdatabasename | database url: jdbc:sqlserver://hostname:1433;databaseName=yourdatabasename |
| | or with parameters, such as: jdbc:sqlserver://hostname:1433;databaseName=db_name;encrypt=true;trustServerCertificate=true; |
| username: the username you created in SQL server Express | username: the username you created in SQL server Express |
| password: the password of the username you created in the SQL server Express | password: the password of the username you created in the SQL server Express |
| |
| Troubleshooting: | 3. Connect to the database. You can write a value to the database with a DatabaseRawWrite, using an array input to trigger the write (array of one element, an integer or small string), with the SQL "insert into tablename (value) values (?);". And you could use a DatabaseRawLookup and use the SQL statement “ Select value from tablename limit 10” to get value. |
| | |
| if you get error in Mistudio console when you try to connect to the database such as: "trustServerCertificate" property is set to "false" but the driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption: Error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target", | |
| | |
| add “encrypt=true;trustServerCertificate=true;” to the URL string, make the database URL as: | |
| | |
| jdbc:sqlserver://hostname:1433;databaseName=db_name;encrypt=true;trustServerCertificate=true; | |
| | |
| 3. Connect to the database. You can write a value to the database with a DatabaseRawWrite, using an array input to trigger the write (array of one element, an integer or small string), with the sql "insert into tablename (value) values (?);". And you could use a DatabaseRawLookup and use the SQL statement “ Select value from tablename limit 10” to get value. | |
| |
| |
| |
| |
| ==Example MIStudio error: “TCP could be blocked by a firewall. No connection for user username”.== | ==** Example MIStudio error: “TCP could be blocked by a firewall. No connection for user username”.== |
| |
| check if TCP/IP port is set in SQL server Configuration Management; if TCP/IP port is disabled in Windows Firewall; if SQL Service Browser process is running. | check if TCP/IP port is set in SQL server Configuration Management; if TCP/IP port is disabled in Windows Firewall; if SQL Service Browser process is running. |
| |
| |
| ==Example MIStudio error: "cannot connect to database. No connection for "com.microsoft.sqlserver.jdbc.SQLServerDrive" for username "username" and password "(hidden)" for URL"jdbc:sqlserver://hostname:1433;databaseName="databasename"."== | ==** Example MIStudio error: "cannot connect to database. No connection for "com.microsoft.sqlserver.jdbc.SQLServerDrive" for username "username" and password "(hidden)" for URL"jdbc:sqlserver://hostname:1433;databaseName="databasename"."== |
| |
| |
| |
| |
| | ==** Example SSMS Log file error: “login failed for user "username". Reason: password did not match that for the login provided by [Client:] SQL server error 18456, state 8."== |
| | |
| ==Example SSMS Log file error: “login failed for user "username". Reason: password did not match that for the login provided by [Client:] SQL server error 18456, state 8."== | |
| |
| | |
