ErgoTechWiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
=====Using SQL Server Express=====

====Overview====

This page details how to configure and use a SQL Server Express database on windows10 with MIStudio.

====Installing SQL Server Express====

Download and install Microsoft SQL Server Express:

[[https://go.microsoft.com/fwlink/p/?linkid=2216019&clcid=0x409&culture=en-us&country=us]]

On the finish screen, you will be asked to install Microsoft SQL Server Management Studio (SSMS), continue the process and install it. Or, you can install SSMS from the Windows "Start” menu afterwards. Go to  Start > All Programs > Microsoft SQL Server Version # > SQL Server Installation Center,  select “Install SQL Server Management Studio” link to install.
 

====Set up Microsoft SQL Server Connection====

Microsoft SQL Server Express needs to have correct settings and parameters for successful connection.
 


===1: Make Sure the Database has TCP/IP Enabled===

MIStudio connects a database using TCP/IP, making sure the database has TCP/IP enabled.

To check if TCP/IP is enabled,

  1. Open the SQL Server Configuration Manager from Start > All Programs > Microsoft SQL Server Version # > Configuration Tools > SQL Server Configuration Manager, the SQL Server Configuration Manager window is displayed.
  2. Expand SQL Server Network Configuration.
  3. Select the instance you want to use. To the right, see all of the protocols the database supports. Find the TCP/IP protocol and select it.
  4. Make sure the Status next to TCP/IP is Enabled. If not, double-click TCP/IP and choose Yes from the drop-down next to Enabled and click OK.
{{:tcp.png?400|}}

  5. Right click on TCP/IP, select Properties. Notice the value of the Listen All item.

{{:tcpproperties.png?400|}}

  6. Now click on the IP Addresses tab in TCP/IP Properties, see a list of IP addresses and their default settings, including IPALL at the end. If the value of Listen All is Yes, the TCP/IP port number for this instance of SQL Server is the value of the TCP Ports item under IPAll.If the value of Listen All is No, the TCP/IP port number for this instance of SQL Server is the value of the TCP Ports item for a specific IP address.

  If Listen All is Yes, enter TCP port number 1433 for IPALL.
  If Listen All is No, enter TCP port number 1433, delete Dynamic TCP Ports number 0, for your specific IP address. 
  If Listen All is no, also make sure "Enabled "is set to “Yes”.

  Click OK.

{{:ipaddress.jpg?400|}}

  7. Open Port 1433 on windows firewall. 
  Open Windows Firewall -> Advanced Configuration and right click on Inbound Rules, and select New Rule, Select "Port" and press Next. Select TCP and enter 1433 in the port number, then press Next.Next two panels leave the defaults, and in the final panel, name the rule "SQL Server Port 1433", then press "Finish".
 
{{:firewallport.png?400|}}


After the change, do a simple test by telnet or netstat. Telnet should get a blank screen indicating no network problem, and netstate should return "listening" to the port.


===2: Make Sure Microsoft SQL Server Browser is Running===

To check if Microsoft SQL Server Browser is running,

  1. Open the SQL Server Configuration Manager from Start > All Programs > Microsoft SQL Server Version # > Configuration Tools > SQL Server Configuration Manager
  2. Select SQL Server Services.
  3. On the right, see SQL Server Browser. Make sure this service is running. If the service is not running, right-click and select Start.


{{:broserserver.png?400|}}







===3: Make sure SQL Authentication is Enabled to Allow Remote Connection===

  1. Open the Microsoft SQL Server Management Studio window from Start > All Programs > Microsoft SQL Server Version # > SQL Server Management Studio.
  
  2. Right-click the top-level database in the Object Explorer and select Properties.
  
{{:properties.png?400|}}

  3. From the Server Properties window, on the left side, select Security.
 
  {{:security.png?400|}}

  4. Verify that SQL Server and Windows Authentication mode is selected. If not, select it.
  
  5. In the “Login auditing” section, select “Failed login only”, so that failed login will be logged in the log file. 
  To view the log  file, go to Management > SQL Server Logs > view > SQL Server and Windows Logs. 
  Click OK. Now you need to take the next two steps to restart the SQL Server Windows service so that this setting takes effect.
  
  6. Open the SQL Server Configuration Manager at Start > All Programs > Microsoft SQL Server Version # > Configuration Tools > SQL Server Configuration Manager
  
  7. Select the SQL Server Services and restart the SQL Server (Instance Name) item. Now Microsoft SQL Server accepts SQL authentication.




 



 

 

 


===4.Add Login and User to the Database===

Add a user account, set username and password for a valid SQL authentication user.

  1. Open the SQL Server Management Studio and expand the Security > Logins folder. You will see all the current logins including sa and you can add a new login here.
  
  2. Right-click on the Logins folder and click New Login....The Login window is displayed.
  
  3. Choose the SQL server authentication mode and type in a Login name and password, un-select all three boxes below (Enforce password policy, Enforce password expiration, and User must change...), select default database. Click OK.
  
  4.  In the Login Properties window, click "User Mapping" and grant access to the database (at least the db_datareader and db_datawriter database roles are required).
  
  5. Go to the database to check if the user has the necessary permissions. To see the permissions for a database, right click on the database, select "properties" at the bottom of the list. When you look at properties for a database there is a panel with choices on the left side. To set permissions for the user, select the user and click on the "permissions" in the list.In this permissions list, select "connect", "alter", "update" "select", and "create table" if they are not checked yet.



    






====Connect to SQL Server Express database with JDBC Driver in MIStudio====

   1. Download and install JDBC driver. See instruction at:
  
[[databasereferencecheatsheet|  https://www.ergotech.com/wiki/doku.php?id=databasereferencecheatsheet]]

   2. Configure the DatabaseConnectionManager to use this driver: 

            database class name: com.microsoft.sqlserver.jdbc.SQLServerDrive
            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
            password: the password of the username you created in the SQL server Express

  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.


====Troubleshooting====

Use MIStudio Error Messages and SSMS log file for troubleshooting:


  ==** 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.



  ==** 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"."==


check if mix mode or SQL authentication is enabled in SSMS, also make sure the user and password can login to SSMS and can do  reading or writing in the database.


  ==** 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."==

 
Check if the username and password can login to SSMS and can do reading and writing in the database. 
 
In case that your database is migrated from another server, “Enable contained database” in SSMS. go to  [Instance Name] > Properties > Advanced > Containment > Enable Contained Databases set to True. 

If the above steps can’t solve the problem, try to use a different user.