Чем подключиться к ms sql server

Connect to MS SQL server

This topic gives you an example of how you can configure Microsoft SQL Server and create a connection to it from DataGrip. If you already have Microsoft SQL Server installed and configured, consider jumping to the section that describes a creation of a database connection in DataGrip.

Step 1. Configure the SQL Server Configuration Manager

To open SQL Server Configuration Manager, open the Search dialog on Windows and type SQLServerManager15.msc (for Microsoft SQL Server 2019). Double-click the found result.

If you use other versions of Microsoft SQL Server, change the second digit before .msc or check the following locations:

SQL Server 2019: C:\Windows\SysWOW64\SQLServerManager15.msc

SQL Server 2017: C:\Windows\SysWOW64\SQLServerManager14.msc

SQL Server 2016: C:\Windows\SysWOW64\SQLServerManager13.msc

SQL Server 2014 (12.x): C:\Windows\SysWOW64\SQLServerManager12.msc

SQL Server 2012 (11.x): C:\Windows\SysWOW64\SQLServerManager11.msc

Run the SQL Server Browser

SQL Server Browser listens for incoming requests and provides information about Microsoft SQL Server instances on the computer. For more information about SQL Server Browser, see SQL Server Browser in the Microsoft documentation.

If the SQL Server Browser menu items are disabled, try to enable the SQL Server Agent service.

In the SQL Server Configuration Manager, click SQL Server Services .

In the right pane that lists server services, right-click SQL Server Browser and select Start .

Enable SQL Server Agent

If the SQL Server Browser is running, you can skip this procedure.

SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks. For more information about the service, see SQL Server Agent in the official Microsoft documentation. Ensure that SQL Server Agent is running and starts automatically. After the change, a restart might be required.

Navigate to Control Panel | Administrative Tools | Services .

In the Services window, right-click SQL Server Agent(<server_name>) and select Properties . For this tutorial, <server_name> is MSSQLSERVER.

From the Startup type list, select Automatic and click Start .

Enable the TCP/IP connection

In the SQL Server Configuration Manager, expand SQL Server Network Configuration and click Protocols for MSSQLSERVER , where MSSQLSERVER is a name of the Microsoft SQL Server instance.

In the list of protocol names, right-click TCP/IP and select Properties .

On the Protocol tab, from the Enabled list, select Yes .

On the IP Addresses tab, find the interface that your workstation uses and see the connection port. By default, the port is 1433. Verify that other running applications do not use the same port.

If you changed any settings, restart the server. For most situations, the restart resolves connection problems.

Step 2. Configure SQL Server Management Studio (SSMS)

Create a user

In the SQL Server Management Studio (SSMS), navigate to Security | Logins .

Right-click the Logins root folder and select New Login .

On the General page, specify a login name in the Login name field.

Select authentication mode. You can select between the following authentication modes:

Windows authentication : to use your domain login and password.

SQL Server authentication : to use a custom login and password. If you select Enforce password security policy checkbox, the user must change the assigned password before connecting to Microsoft SQL Server with DataGrip. Otherwise, clear the Enforce password security policy checkbox.

Configure user roles

Right-click the created user profile and select Properties .

On the Server Roles page, select the appropriate user role (for example, sysadmin ).

Step 3. Connect to Microsoft SQL Server with DataGrip

The following section describes configuration of DataGrip on Windows, macOS, and Linux. Note that the Use Windows domain authentication checkbox is available only on Windows. To configure Windows domain authentication on macOS and Linux, see Connect by using Windows domain authentication.

Windows

Connect by using SQL Server authentication

Navigate to File | Data Sources or press Ctrl+Alt+Shift+S .

In the Data Sources and Drivers dialog, click the Add icon () and select Microsoft SQL Server .

Click the Driver link and select Microsoft SQL Server (jTds) .

Check if there is a Download missing driver files link at the bottom of the data source settings area. As you click this link, DataGrip downloads drivers that are required to interact with a database. The IDE does not include bundled drivers in order to have a smaller size of the installation package and to keep driver versions up-to-date for each IDE version.

You can specify your drivers for the data source if you do not want to download the provided drivers. For more information about creating a database connection with your driver, see Add a user driver to an existing connection.

In Host , Instance , and Port fields, specify your connection details. If the server name (in our case, DESKTOP ) does not work, replace it with the server IP address.

From the Authentication list, select User & Password .

In User and Password fields, specify your credentials.

To ensure that the connection to the data source is successful, click the Test Connection link.

(Optional) If you are connecting to a data source that contains a lot of databases and schemas, in the Schemas tab, select the schemas that you need to work with.

Click OK to create the data source.

Find your new data source in Database Explorer .

To write and run queries, open the default query console by clicking the data source and pressing F4 .

To view and edit data, use Data editor.

To learn how to work with database objects in DataGrip, see Database objects.

Connect by using single sign-on for Microsoft SQL Server

If you run DataGrip on Windows in the same domain as the Microsoft SQL Server database, you can use the Single-Sign On (SSO).

Navigate to File | Data Sources or press Ctrl+Alt+Shift+S .

In the Data Sources and Drivers dialog, click the Add icon () and select Microsoft SQL Server .

Click the Driver link and select Microsoft SQL Server (jTds) .

Check if there is a Download missing driver files link at the bottom of the data source settings area. As you click this link, DataGrip downloads drivers that are required to interact with a database. The IDE does not include bundled drivers in order to have a smaller size of the installation package and to keep driver versions up-to-date for each IDE version.

You can specify your drivers for the data source if you do not want to download the provided drivers. For more information about creating a database connection with your driver, see Add a user driver to an existing connection.

In Host , Instance , and Port fields, specify your connection details. If the server name (in our case, DESKTOP ) does not work, replace it with the server IP address.

From the Authentication list, select Windows credentials .

To ensure that the connection to the data source is successful, click the Test Connection link.

(Optional) If you are connecting to a data source that contains a lot of databases and schemas, in the Schemas tab, select the schemas that you need to work with.

Click OK to create the data source.

Find your new data source in Database Explorer .

To write and run queries, open the default query console by clicking the data source and pressing F4 .

To view and edit data, use Data editor.

To learn how to work with database objects in DataGrip, see Database objects.

Connect by using Windows domain authentication

Navigate to File | Data Sources or press Ctrl+Alt+Shift+S .

In the Data Sources and Drivers dialog, click the Add icon () and select Microsoft SQL Server .

Click the Driver link and select Microsoft SQL Server (jTds) .

Check if there is a Download missing driver files link at the bottom of the data source settings area. As you click this link, DataGrip downloads drivers that are required to interact with a database. The IDE does not include bundled drivers in order to have a smaller size of the installation package and to keep driver versions up-to-date for each IDE version.

You can specify your drivers for the data source if you do not want to download the provided drivers. For more information about creating a database connection with your driver, see Add a user driver to an existing connection.

In Host , Instance , and Port fields, specify your connection details. If the server name (in our case, DESKTOP ) does not work, replace it with the server IP address.

From the Authentication list, select Domain credentials .

In the Domain field, specify the domain (for example, DEVELOPMENT ).

In User and Password fields, specify your domain credentials. In the User field, type your domain user without the domain prefix (for example, John.Smith instead of DOMAIN\John.Smith ).

Alternatively, on the General tab, specify the connection string. Consider the following example of a full connection string:

To ensure that the connection to the data source is successful, click the Test Connection link.

(Optional) If you are connecting to a data source that contains a lot of databases and schemas, in the Schemas tab, select the schemas that you need to work with.

Click OK to create the data source.

Find your new data source in Database Explorer .

To write and run queries, open the default query console by clicking the data source and pressing F4 .

To view and edit data, use Data editor.

To learn how to work with database objects in DataGrip, see Database objects.

macOS and Linux

Connect by using SQL Server authentication

Navigate to File | Data Sources or press ⌘ ; .

In the Data Sources and Drivers dialog, click the Add icon () and select Microsoft SQL Server .

Click the Driver link and select Microsoft SQL Server (jTds) .

Check if there is a Download missing driver files link at the bottom of the data source settings area. As you click this link, DataGrip downloads drivers that are required to interact with a database. The IDE does not include bundled drivers in order to have a smaller size of the installation package and to keep driver versions up-to-date for each IDE version.

You can specify your drivers for the data source if you do not want to download the provided drivers. For more information about creating a database connection with your driver, see Add a user driver to an existing connection.

In Host , Instance , and Port fields, specify your connection details. If the server name (in our case, DESKTOP ) does not work, replace it with the server IP address.

From the Authentication list, select User & Password .

In User and Password fields, specify your credentials.

To ensure that the connection to the data source is successful, click the Test Connection link.

(Optional) If you are connecting to a data source that contains a lot of databases and schemas, in the Schemas tab, select the schemas that you need to work with.

Click OK to create the data source.

Find your new data source in Database Explorer .

To write and run queries, open the default query console by clicking the data source and pressing F4 .

To view and edit data, use Data editor.

To learn how to work with database objects in DataGrip, see Database objects.

Connect by using Windows domain authentication

Navigate to File | Data Sources or press ⌘ ; .

In the Data Sources and Drivers dialog, click the Add icon () and select Microsoft SQL Server .

Click the Driver link and select Microsoft SQL Server (jTds) .

Check if there is a Download missing driver files link at the bottom of the data source settings area. As you click this link, DataGrip downloads drivers that are required to interact with a database. The IDE does not include bundled drivers in order to have a smaller size of the installation package and to keep driver versions up-to-date for each IDE version.

You can specify your drivers for the data source if you do not want to download the provided drivers. For more information about creating a database connection with your driver, see Add a user driver to an existing connection.

In Host , Instance , and Port fields, specify your connection details. If the server name (in our case, DESKTOP ) does not work, replace it with the server IP address.

From the Authentication list, select Domain credentials .

In the Domain field, specify the domain (for example, DEVELOPMENT ).

In User and Password fields, specify your domain credentials. In the User field, type your domain user without the domain prefix (for example, John.Smith instead of DOMAIN\John.Smith ).

Alternatively, on the General tab, specify the connection string. Consider the following example of a full connection string:

To ensure that the connection to the data source is successful, click the Test Connection link.

(Optional) If you are connecting to a data source that contains a lot of databases and schemas, in the Schemas tab, select the schemas that you need to work with.

Click OK to create the data source.

Find your new data source in Database Explorer .

To write and run queries, open the default query console by clicking the data source and pressing F4 .

To view and edit data, use Data editor.

To learn how to work with database objects in DataGrip, see Database objects.

Подключение к базе данный MS SQL Server из Visual Studio Code

22.12.2022
itpro
SQL Server
Комментариев пока нет

Для управления базами данных на Microsoft SQL Server вместо SQL Server Management Studio (SSMS) можно использовать легкий кроссплатформенный редактор Visual Studio Code (VS Code). Visual Studio Code с расширением mssql позволяет администраторам и разработчикам управлять базами данных, таблицами, хранимыми процедурами, выполнять Transact-SQL (T-SQL) запросы к on-prem базам данных MS SQL Server и Azure SQL Database.

Если на вашем компьютере уже установлен VSCode, вам нужно скачать и установить расширение mssql (https://marketplace.visualstudio.com/items?itemName=ms-mssql.mssql)

Перейдите в раздел Extension ( Ctrl+Shift+X ), вбейте в поиск mssql ;

Попробуем теперь из VS Code удалено подключиться к хосту установленным MS SQL Server 2019:

Щелкните по строке Add connection и укажите имя сервера и инстанса MS SQL ( hostname\instance_name );

В этом случае проверьте, что в настройка MSSQL на вкладке Security выбран режим аутентификации SQL Server and Windows Authentication mode.

В VS code для кода T-SQL поддерживается весь функционал IntelliSense: работают подсказки, автодополнение команд, цветовые схемы для разных объектов и т.д. Также Visual Studio Code поддерживает функцию интеллектуальных фрагментов SQL кода для быстрой генерации типовых SQL запросов.

Setting up Management Studio for remote SQL Server

Most of us, developers, love to write SQL code on our local machines not only because they’re executed faster, but also because we have a lot more control over the data as it is used only by us and is not team dependent. This was the case in my team until recently when the policy has changed and the new policy requires us to develop using a centralized database that was being hosted on one of the on-premise servers rather than using local SQL Servers for each individual developers.

There are both advantages and disadvantages to this approach. You can free up some space and RAM from your local machine by disabling the SQL Server Services, which can be an advantage for some. However, you’d need to connect to the server now and write queries keeping in mind that other developers are also using the same database. So, one should be aware of making any changes to the data.

The important steps to allow remote connections to an instance can be divided into the following parts, which are discussed in detail below.

1. Allowing Remote Connections
2. Add Remote User to the database
3. Configuring the SQL Server Instance
4. Configuring Windows Firewall
5. Fetch the Connection Details
6. Connecting to the remote machine

This article describes the steps on how to configure remote access on a SQL Server instance and connect to it from a developer’s machine using SSMS.

Connect to a Remote SQL Server

In order to be able to connect to the remote database, we need to configure it to allow remote machines to connect to it.

Allowing Remote Connections

1. Right-click the on the SQL Server instance name and select Properties.
2. Select Connections on the left-hand pane.
3. Under Remote Server Connections, check the box against «Allow remote connections to this server«.
4. Leave the default value for the Remote query timeout to 600.
5. Click OK.

The SQL Server is not configured to be able to allow remote machines to connect to this instance.

Add a Remote User to the database

Once the SQL Server is configured, we need to allow decide users should be able to connect and access the database objects. This is done by creating a SQL Login and then assigning specific database roles to those users.

In this case, since all the machines are in the same domain, we will proceed with the Windows Authentication mode.

1. Connect to the SQL Server instance as a server admin.
2. Expand Security and right-click on Logins.
3. Select New Login.
4. The Login — New dialog box appears.
5. On the General page, click Search.
6. The Search User, Service Account or Group dialog box appears.
7. Type the username in the object name text box and click Check Names.
8. This should automatically fetch the user details if there are any users with the same username.
9. Keep authentication mode as Windows Authentication.
10. Now, select Server Roles on the left-hand Page and select the checkbox against Public.
11. Navigate to User Mappings, and select the database on which the user needs access to.
12. We can assign any specific role as security demands. In this case, I’m assigning as db_owner.
13. On the Status page, select Permission as Grant and Login as Enabled and click OK.
14. The new login will be visible under the logins in the SQL Server Object Explorer.

Configuring the SQL Server Instance

Now that the database instance is configured for remote connections, we need to allow remote traffic to be allowed on the server. This can be done by following the steps below:

1. Select SQL Server 2016 Configuration Manager from the Start.
2. Navigate to Protocols for <<YOUR SERVER NAME>> under SQL Server Network Configuration on the left-hand pane.
3. Make sure that the TCP/IP Protocol Name is Enabled.

Once the TCP/IP Protocol is Enabled, we need to configure the port on which the SQL Server will allow inbound traffic.

1. Right-click on TCP/IP Protocol Name and select the Property option.
2. Navigate to the IP Addresses tab and scroll down to the section named «IPAII«.
3. If the TCP Dynamic Ports is set to 0 (indicates the Database Engine is listening on dynamic ports), then remove the 0 and set it to blank.
4. Update the value for TCP Port to 1434. This is the default port that is being used by the SQL Server Database Engine and click OK.
5. A warning might be displayed which will prompt to restart the service.

Now that the IP Ports are configured, we need to restart the SQL Server service, which will run the Database Engine on the ports as specified earlier.

1. Select SQL Server Services.
2. On the right-hand panel, right-click the SQL Server (Instance Name) and select Restart.
3. The service will be restarted.

Configuring Windows Firewall

Once the database instance is configured to accept remote connections, we need to add an exception in the Windows Firewall for the port (1434) to allow TCP/IP traffic on this port.

1. Click Start and select Administrative Tools.
2. In the Administrative Tools window, double-click Windows Firewall with Advanced Security.
3. The Windows Firewall with Advanced Security window opens.
4. Click Inbound Rules on the left-hand panel.
5. On the right-hand panel, click New Rule under Actions.
6. The New Inbound Rule Wizards starts.
7. Select the Port option under Rule Type and then click on Next.
8. Under Protocols and Ports, select TCP and Specified local ports as «1434» (since we want to allow traffic on this port).
9. Click Next.
10. Select Allow the connection under Action and click on Next.
11. Select all the three options under Profile (This depends on whom do you want to allow access to the database instance).
12. Click Next.
13. The final step is to provide a Name to this new rule.
14. The Description is not mandatory, so you may choose not to fill it in.
15. Finally, click Finish.
16. The new rule should now be visible in the Inbound Rules section.

Fetch the Connection Details

Now that everything has been set up properly to allow inbound traffic over the firewall, we are ready to connect to this machine from any other machine in the network. For this, we need to get the correct IP Address of the machine and the Instance Name of the database (only in case of a named instance).

1. Open Command Prompt and type ipconfig.
2. Copy the IPv4 Address. In this case, it is «10.13.106.38«.
3. Also, open SQL Server Configuration Manager.
4. Select SQL Server Services on the left-hand panel and copy the name of the instance of the database. This is only required in case the database is running on a named instance.
5. In this case, the name of the instance is SQLTEST01.

Connecting to the remote machine

Once the remote server is configured, now we should be able to connect to the SQL Database Instance from any other PCs.

1. Open SQL Server Management Studio.
2. Provide the Server name in the format <<REMOTE MACHINE IP>>\<<INSTANCE NAME>>.
3. In this case, it is 10.13.106.38\SQLTEST01.
4. Since I’m in the same domain as the remote server, I’d be using Windows Authentication.
5. Click Connect.
6. The remote database instance should be now accessible from the local machines.
7. Now you should be able to browse all the databases that the user has been granted access with.

Take Away

This article describes how to set up a remote server to allow other users to connect to a named instance of the SQL Server Database Engine.

WPF Подключение к базе данных MS SQL Server

Windows Presentation Foundation — один из компонентов ядра .NET Framework, который позволяет разработчикам создавать богатые, различные Windows приложения.

Что такое MS SQL Server?

Система управления реляционными базами данных (РСУБД), разработанная корпорацией Microsoft. Основной используемый язык запросов — Transact-SQL, создан совместно Microsoft и Sybase. Transact-SQL является реализацией стандарта ANSI/ISO по структурированному языку запросов с расширениями. Используется для работы с базами данных размером от персональных до крупных баз данных масштаба предприятия, конкурирует с другими СУБД в этом сегменте рынка.

Как подключиться к Базе Данных MS SQL Server?

Для того, чтобы осуществить подключение к базе данных MS SQL в WPF приложении, нужно создать базу данных (БД) и таблицы. Для начала стоит открыть приложение Microsoft SQL Server Management Studio 2017 или более раннюю версию, и произвести соединение с SQL сервером.

Далее правой кнопкой мыши (ПКМ) нужно щёлкнуть по «Базы данных» и выбрать пункт «Создать базу данных».

Указать название базы данных — TEST и нажать «ОК». База данных создана, теперь следует добавить таблицу. Для этого нужно открыть иерархию в «Обозреватель объектов», и на пункте «Таблицы» нажать ПКМ, выбрав «Создать» — «Таблица».

Далее следует ввести название полей и тип данных как указано на рисунке 4 и нажать комбинацию клавиш Ctrl+S или иконку сохранения в панели инструментов.

После завершения возникнет окно, с просьбой указать имя таблицы, её следует назвать «users». После нужно обновить «Обозреватель объектов» и можно увидеть, что данная таблица появилась в иерархии: «Название сервера» — «Базы данных» — «TEST» — «Таблицы» — «dbo.users». Далее нужно нажать по таблице и выбрать пункт «изменить первые 200 строк». Откроется пустая таблица.

Добавляем запись щёлкая в нужные ячейки таблицы ЛКМ:

• login – student
• password – asdfg123

Теперь нужно перейти к приложению WPF и у главной формы (MainWindow.xaml.cs) прописать следующую функцию:

Важно отметить что весь текст у будет отмечен красным а при подключении будут возникать ошибки. Вся проблема в том, что нужно добавить несколько библиотек, которые позволяют работать с базой данных, для этого, нужно нажать ПКМ по подчёркнутому красной линией элементу и выбрать пункт «Быстрые действия и рефакторинг. », после из предложенных действий выбрать первый вариант который добавляет библиотеку.

Так же следует сделать и с SqlConnection, добавляя библиотеку System.Data.SqlClient.

В итоге должно появиться две библиотеки:

• using System.Data;
• using System.Data.SqlClient;

Разбор строки подключения к базе данных:

• server – здесь указывается имя сервера, которое было представлено на рисунке 1. (В моём случае, это DESKTOP-NBH5JF2);
• DataBase — название базы данных к которой происходит подключение;
• Trusted_Connection – безопасное подключение, используется только в тех случаях если сервер установлен локально и при подключении происходит проверка подлинности Windows;

Кроме трёх стандартных свойств подключения, так же могут понадобиться такие атрибуты как User и PWD, если сервер будет на удалённой машине. В таком случае строка подключения будет выглядеть следующим образом:

где вместо звёздочек используется ваш логин и пароль, а вместо IP – IP сервера.

После того как ошибки исчезли и приложение запускается, стоит научиться пользоваться данной функцией. Для этого после инициализации компонентов нужно прописать код: