SQL Queries

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications, which may run either on the same computer or on another computer across a network (including the Internet).

Microsoft makes SQL Server available in multiple editions, with different feature sets and targeting different users.

This document will focus on SQL Server Express Edition, which is a scaled down, free edition of SQL Server and includes the core database engine.

SQL Server Management Studio

SQL Server Management Studio is a GUI tool included with SQL Server 2005 and later that is used for configuring, managing, and administering all components within Microsoft SQL Server. The tool includes both script editors and graphical tools that work with objects and features of the server.

SQL Server Management Studio can also be used to create a new database, alter any existing database schema by adding or modifying tables and indexes, or analyze performance. It includes the query windows, which provide a GUI based interface to write and execute queries.

Create a New Account

SQL Server Authentication is a default mechanism to connect to MS SQL Server, but there are times that you need to use SQL Server Authentication to connect to the server.

In order to use SQL Server Authentication, you need to create a SQL Server Authentication Login ID.

1. Run Microsoft SQL Server Management Studio.

2. Expand the Security item in the Object Explorer, right-click on Logins, and choose New Login.

3. Enter an account name in the Login name field and choose SQL Server authentication. Then, enter a password for the account.

*Note: Uncheck the following options to set the password as you want:

• Enforce password policy
• Enforce password expiration
• User must change password at next login

If the options are checked, you should set the password according to the security policy in Windows.

4. Choose master from the Default database drop-down list.

5. Go to the Server Roles page and select sysadmin.

6. Go to the User Mapping page and choose master and db owner.

7. Go to the Status page. Set the Permission to connect to database engine to Grant and set Login to Enabled.

8. Disconnect the database server in the Object Explorer and reconnect to it using the ID you’ve just created.

9. Choose SQL Server Authentication from the Authentication drop-down list and enter the ID and password. Then, click Connect.

Connecting With Factory Studio

Go to Datasets > DBs, on the Available Providers ListBox select SqlClient Data Provider, and choose SQL Server.

The following configurations require the user to be logged in as an Administrator.

In the LogonName column insert the newly created user. In the LogonPassword column insert the password.

In the ConnectionString column, the fields are configured as in the image.