Как сделать подключение Excel к базе MS SQL

Добрый день! Уважаемые читатели и гости портала. Я не так часто пишу статьи по офисному пакету, но сегодня подвернулась возможность это исправить. Microsoft Excel представляет собой эффективный инструмент для анализа данных SQL. Используя его, вы можете установить прямое подключение к нужной базе данных, настроив фильтрацию согласно вашим потребностям. Это позволяет импортировать данные SQL в Excel, визуализировать их в виде таблицы, формировать сводные отчёты и значительно упростить работу с информацией из базы данных MS SQL.

Описание задачи

Опиши некоторые сценарии, которые я рассмотрю в данной статье.

1. Есть лес Active Directory в рамках которого несколько транзитивных доменов. Потребовалось пользователю из одного домена затянуть данные из таблицы SQL в файл Excel, для последующей обработки.
2. Вторая ситуация, это есть два независимых леса Active Directory, между ними нет доверительных отношений, но есть сетевая связанность. Пользователю леса А нужно подключить Excel к базе данных MS SQL леса Б.

Стандартное получение данных MS SQL в Excel

Я буду показывать подключение на примере Microsoft Excel 2021. Тут рассматриваю первый сценарий, когда учетные данные пользователя находят в одном лесу с сервером. Перейдите на вкладку "Данные — Подключить данные". Выберите пункт "Из базы данных — Из базы данных SQL Server".

В английском варианте - "Data - Get Data -From Database - From SQL Server Database"

У вас появится окно по подключению базы данных SQL Server. Вам необходимо указать адрес сервера в формате fqdn-имя\имя инстанса если есть. В моем примере это сервер 1cdev03.pyatilistnik.org\SQL2019. Для базового подключения вам не потребуется заполнение дополнительных полей.

Далее у вас появится окно, где необходимо выбрать какие учетные данные вы будите использовать для подключения к БД. На выбор будет три варианта:

• Windows аутентификация — тут будут подставляться данные, из-под которых вы работает в рамках текущей сессии, либо вы можете указать доменную учетную запись от другого пользователя в формате NTLM (домен\логин) или Kerberos (логин@UPN-суффикс).

• База данных — Тут вам предлагают указать локальные учетные данные созданные на самом SQL сервере.

• Учетная запись Майкрософт — Если у вас облачный сервер MS SQL и вам нужно использовать учетку Microsoft365.

Если на данном этапе у вас окажутся какие-то проблемы с сетевой связанностью до сервера, разрешением DNS имени, учетными данными, то вы увидите подобную ошибку. Тут вам потребуется провести диагностику.

Подробности: "Microsoft SQL: При установлении соединения с SQL Server произошла ошибка, связанная с сетью или с определенным экземпляром. Сервер не найден или недоступен. Убедитесь, что имя экземпляра указано правильно и что на SQL Server разрешены удаленные соединения. (provider: SQL Network Interfaces, error: 26 - Ошибка при обнаружении указанного сервера или экземпляра)"

Что можно проверить: утилитой ping проверить доступность, если есть проблемы, можно попытаться утилитой pathping посмотреть на каком этапе. Далее протестировать доступность порта. Проверить не блокируется ли трафик антивирусным решением. Удостовериться через SQL Server Management Studio, что производится подключение с используемыми учетными данными пользователя.

Если все доступно и введено правильно, у вас откроется окно навигатора. В нем вы увидите хост MS SQL и список доступных баз данных. Выберите нужную и нажмите загрузить данные.

В итоге вы увидите заполненные данные из базы данных. Далее вы уже можете с ними взаимодействовать как вам будет угодно, например вывести сводную таблицу.

Подключение базы в Excel с компьютера не входящего в лес AD, где работает SQL сервер

В данном сценарии я я буду подключаться с рабочей станции из леса А (можно и некорпоративной системы) к серверу SQL из леса Б, используя учетные данные из леса Б.

Тут подразумевается, что между ними есть сетевая связанность и все остальное, как я указывал выше.

В этой ситуации нам поможет утилита командной строки RunAs, с которой я вас уже ранее знакомил. Запустите cmd и перейдите в нем в расположение исполняемого файла Excel.exe. Для этого введите и нажмите Enter:

cd C:\Program Files\Microsoft Office\root\Office16

Теперь находясь в нужном каталоге, вам необходимо выполнить команду, где потребуется указать свой домен и логин пользователя:

runas /netonly /user:root\barboskin.g EXCEL.EXE

У вас будет запрошен его пароль. Укажите его и нажмите Enter. Если все введено верно, то будет запущен Microsoft Excel, но уже с учетными данными другого пользователя.

Выберите в Exсel нужный файл или создайте новый. Перейдите на вкладку "Данные — Подключить данные". Выберите пункт "Из базы данных — Из базы данных SQL Server" и дойдите до окна выбора метода авторизации. Теперь просто оставляем пункт "Использовать учетные данные Windows для доступа к этой базе данных" и нажмите "Подключение". Все должно подключиться.

Обратите внимание, что после успешного входа у вас будет закэшированно это подключение, чтобы вам повторно не пришлось вводить учетные данные пользователя. Вы можете увидеть, что теперь в списке последних источников (Queries & Connections) будет ваш сервер.

Как поменять учетную запись для подключения БД SQL в Excel

Предположим, что вам потребовалось сделать подключение базы данных SQL от имени другого пользователя. Для этого переходим в пункт "Получить данные - Параметры источника данных".

Выбираем нужное подключение, после чего нажимаем копку "Править разрешения". В новом окне нажмите кнопку "Изменить". После чего задайте новые учетные данные.

Как очистить список последних источников

Чтобы удалить нужное подключение из списка, вызовите окно. Далее щелкните правым кликом и из контекстного меню выберите пункт "Удалить".

Как удалить источники подключения

Для того чтобы удалить источник данных в текущей книге, вам нужно в области "Запросы и подключения" в контекстном меню выбрать пункт "Удалить".

Если нужно удалить глобальный источник, то вам необходимо удалить учетные записи из-под которых вы производили подключение. Для этого в правке разрешений удаляем.

В результате данный источник пропадет из списка.

Как обновить данные в Excel после импорта из SQL

Если вам нужно обновить актуальные данные из SQL таблицы в Excel, то вам нужно выбрать соответствующую кнопку. Тут будет обновить все или конкретную ячейку.

На этом у меня все. Надеюсь, что было полезно. С вами был Иван Сёмин, автор и создатель IT портала Pyatilistnik.