Contents 호환성 문제의 정의와 증상 분류 즉시 조치 체크리스트(빠른 복구) 파일 형식별 특성과 선택 기준 버전 차이로 인한 기능 손실과 대체 전략 조직 환경 차이: 32/64비트, 추가기능, 보안 정책 CSV·텍스트 파일 호환성: 인코딩·구분 기호·서식 날짜·숫자 변형 방…
글 전체 보기- 공유 링크 만들기
- X
- 이메일
- 기타 앱
이 글의 목적은 엑셀에서 외부 데이터 연결이 실패하거나 새로 고침이 되지 않을 때, 현장에서 즉시 적용 가능한 진단 절차와 설정값, 점검 체크리스트, 복구 노하우를 체계적으로 제공하는 것이다.
1. 문제를 정확히 정의하다
데이터 연결 문제는 원인 범위가 넓기 때문에 첫 단계에서 증상을 유형별로 구분하여 해결 시간을 단축해야 한다.
| 증상 | 주요 트리거 | 대표 로그/메시지 |
|---|---|---|
| Power Query 새로 고침 실패 | 자격 증명 변경, 프라이버시 수준 충돌, 게이트웨이/프록시, 스키마 변경 | “자격 증명 필요”, “개인정보 수준 충돌”, “열을 찾을 수 없음” |
| ODBC/OLE DB 연결 실패 | 드라이버 비호환(32/64비트), TLS/포트 차단, DSN 손상 | “데이터 원본에 연결할 수 없습니다” “서버에 연결할 수 없습니다” |
| 웹/SharePoint/OneDrive 소스 오류 | URL 리디렉션, 인증 방식 변경, 만료 토큰 | “웹 콘텐츠에 액세스할 수 없습니다” “권한 없음(401/403)” |
| 외부 통합문서 링크 값 불러오기 실패 | 파일 경로 변경, 상대경로/UNC 혼용, 보호된 보기 | “외부 링크를 업데이트할 수 없습니다” |
주의 : 동일 증상이라도 소스 종류(데이터베이스/웹/파일/통합문서)에 따라 해결 레버가 다르다. 이후 절을 순서대로 진행하되, 해당 소스 유형에 맞는 항목을 우선 적용한다.
2. 빠른 진단 10단계(현업 체크리스트)
- 파일 백업 후 데이터 → 연결 및 쿼리 창에서 실패 쿼리만 별도 테스트하다.
- 현재 오피스 비트 수(32/64)와 데이터 드라이버 비트 수를 일치시키다.
- 신뢰할 수 있는 위치에 파일을 두고 외부 콘텐츠 활성화를 허용하다.
- 회사 프록시/방화벽 정책 하에서 필요한 포트·도메인이 허용되는지 네트워크팀과 확인하다.
- 자격 증명 캐시를 삭제하고 올바른 인증 방식으로 재로그인하다.
- 프라이버시 수준을 동일 소스끼리 일관되게 지정하다.
- 스키마 변경(열명·형식 변경)을 Power Query 단계에서 방어적 변환으로 흡수하다.
- 상대경로/UNC를 표준화하고 링크 관리자에서 모두 바꾸기로 경로를 일괄 교정하다.
- 쿼리 명령 시간 제한을 충분히 늘리고, 배경 새로 고침 설정을 상황에 맞게 조정하다.
- 손상된 연결은 새 쿼리로 재작성하고, 기존 쿼리는 단계별 비교로 차이를 기록하다.
3. 보안·신뢰 설정 점검
3.1 신뢰 센터
- 파일 → 옵션 → 신뢰 센터 → 신뢰 센터 설정을 열다.
- 외부 콘텐츠에서 데이터 연결 보안 설정을 “사용”으로 조정하고, 조직 정책에 맞게 구체 옵션을 선택하다.
- 신뢰할 수 있는 위치에 현재 파일 경로를 추가하다.
- 보호된 보기에서 인터넷에서 내려받은 파일이라면 첫 실행만 편집 가능하도록 조직 기준을 따른다.
주의 : 무분별한 전역 허용은 피싱 및 악성 매크로 노출을 크게 높인다. 조직 표준 운영 절차(SOP)를 준수해야 한다.
3.2 자격 증명 캐시 재설정
- 데이터 → 데이터 원본 설정에서 소스를 선택하고 권한 편집을 눌러 저장된 자격 증명을 지우기 한 후 다시 로그인하다.
- 회사 단일 로그온(SSO) 환경이면 브라우저/Office 계정 동기화를 확인하다.
4. Power Query 원인별 해결
4.1 프라이버시 수준 충돌
서로 다른 프라이버시 수준의 소스 결합 시 결합이 차단되거나 성능 저하가 발생한다. 동일 보안 영역끼리 묶고, 불가피할 때는 목적에 맞게 프라이버시 수준을 통일한다.
| 구분 | 설정 예 | 권장 사례 |
|---|---|---|
| 개인 | 로컬 파일, 데스크톱 경로 | 사내 결합 불가. 업무 데이터와 분리하다. |
| 조직 | 사내 DB, SharePoint, OneDrive for Business | 사내 소스 결합 기본값으로 사용하다. |
| 공용 | 공개 웹 데이터 | 사내 데이터와 결합 시 주의하다. |
주의 : 프라이버시 수준을 무조건 “없음”으로 두는 것은 데이터 유출 위험이 있다. 결합 필요성, 데이터 민감도, 규정을 고려하여 결정한다.
4.2 스키마 변경 대응
소스 열명·형식이 바뀌면 “열을 찾을 수 없음” 오류가 난다. 방어적 쿼리 패턴으로 변경에 견고하게 만든다.
// 예: 열 이름이 바뀌어도 존재 시만 변환 적용 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Cols = Table.ColumnNames(Source), Step1 = if List.Contains(Cols, "Amount") then Table.TransformColumnTypes(Source, {{"Amount", type number}}) else Source, Step2 = if List.Contains(Cols, "Date") then Table.TransformColumnTypes(Step1, {{"Date", type date}}) else Step1 in Step2 4.3 성능·시간 제한
- 쿼리 속성에서 명령 시간 제한을 늘리다.
- 필요 시 배경 새로 고침을 해제하고 동기 새로 고침으로 실패 원인을 즉시 확인하다.
- 필터·집계는 소스에서 처리되도록 쿼리 폴딩을 유지하는 변환 순서를 설계하다.
4.4 파일 커넥터 경로 전략
팀 공유에서는 UNC 경로(예: \\Server\Share\...)를 사용하여 드라이브 문자 의존을 제거한다. 버전 관리에는 SharePoint/OneDrive Business 동기화를 우선한다.
# 상대경로 예시: .pbix와 .xlsx 구조 동일 시 .\Data\Sales_2025.csv 주의 : Windows 드라이브 매핑(
Z: 등)은 사용자·서버 재로그인 시 달라질 수 있다. UNC로 표준화하는 것이 안전하다.5. ODBC/OLE DB 연결 실패 해결
5.1 드라이버 호환성
- 엑셀 비트 수와 드라이버 비트 수를 일치시키다.
- 가능하면 공급사에서 권장하는 최신 드라이버를 사용하다.
- DSN 사용 대신 DSN 없는 연결을 고려하여 배포 용이성을 확보하다.
5.2 네트워크·TLS
DB 포트와 TLS 정책 차이로 실패하는 경우가 많다. 다음을 점검한다.
| 데이터베이스 | 기본 포트 | 점검 포인트 |
|---|---|---|
| SQL Server | 1433 | 명명된 인스턴스·동적 포트, 암호화 강제 |
| MySQL/MariaDB | 3306 | TLS 버전, 서버 인증서 체인 |
| Oracle | 1521 | 서비스명/SID, EZCONNECT 허용 |
| PostgreSQL | 5432 | pg_hba 정책, SSL 모드 |
REM 네트워크 기본 점검 ping db.company.local nslookup db.company.local powershell Test-NetConnection db.company.local -Port 1433 주의 : 회사 보안정책으로 ICMP가 차단될 수 있다. 포트 테스트 결과를 우선 신뢰한다.
5.3 연결 문자열 검증
Provider=SQLOLEDB;Data Source=SERVER,1433;Initial Catalog=DB; Integrated Security=SSPI;Encrypt=True;TrustServerCertificate=False; Encrypt·TrustServerCertificate 조합은 서버 인증서 상태에 맞춰 조정한다. 불필요한 Trusted_Connection 중복은 제거한다.
6. 웹/SharePoint/OneDrive 연결
6.1 인증 모드
- 조직 계정(Office 365)으로 로그인하여 회사 또는 학교 계정 인증을 사용하다.
- 개인 OneDrive/공용 SharePoint 링크를 혼용하지 않다.
- 링크는 “브라우저 보기”가 아닌 파일 직접 다운로드 URL을 사용하다.
6.2 링크 안정화
링크가 리디렉션되면 새로 고침에 실패한다. Power Query의 웹 컨텐츠 단계에서 최종 URL을 확인하고 고정한다.
7. 외부 통합문서 링크(공식·값) 문제
7.1 경로 표준화
- 데이터 → 연결 편집 → 링크 편집에서 원본 변경으로 UNC 경로로 통일하다.
- 수식 → 이름 관리자에서 외부 참조가 내장된 이름 정의를 확인하고 경로를 교정하다.
7.2 보호된 보기와 자동 계산
보호된 보기에서 외부 링크는 차단된다. 편집 사용 후 계산 옵션이 자동인지 확인한다.
8. 조직 환경에서의 배포 전략
8.1 연결 매개변수 분리
서버·DB·경로는 별도 매개변수 쿼리 및 환경 테이블로 관리하여 이관 시 수정 범위를 최소화한다.
// 매개변수 예시 let Server = Excel.CurrentWorkbook(){[Name="Env"]}[Content]{0}[Server], DB = Excel.CurrentWorkbook(){[Name="Env"]}[Content]{0}[DB], Source = Sql.Database(Server, DB) in Source 8.2 오류 로깅
새로 고침 직후 오류 메시지를 캡처하는 로깅 시트를 두면 재현과 보고가 쉬워진다.
' 간단 VBA: 모든 쿼리 새로 고침 + 완료 대기 Sub RefreshAllQueries() Application.CalculateFull ThisWorkbook.RefreshAll DoEvents Dim qt As QueryTable For Each qt In ActiveSheet.QueryTables Debug.Print qt.ResultRange.Address Next qt End Sub 주의 : VBA 사용이 금지된 조직도 있다. 배포 전 보안정책을 확인한다.
9. 문제 유형별 정밀 가이드
9.1 “자격 증명 필요” 반복
- 데이터 원본 설정에서 동일 호스트에 대한 모든 항목 자격 증명을 삭제하다.
- 웹 소스는 익명/기본/Windows/조직 계정 중 실제 지원 방식을 선택하다.
- 프록시 로그인과 Office 계정이 상충하지 않도록 브라우저 캐시를 비운다.
9.2 “열을 찾을 수 없음”
- 소스 스키마 변경 내역을 확보하다.
- 초기 단계에서 선택된 열만 유지 패턴을 피하고, 존재 조건부 변환으로 대체하다.
9.3 “데이터 원본에 연결할 수 없습니다”
- 네트워크 포트·DNS·TLS 검증을 선행하다.
- 드라이버 암호화 옵션과 서버 인증서 정책을 일치시키다.
9.4 외부 링크 업데이트 실패
- 링크 관리자에서 경로를 UNC로 통일하다.
- 원본 파일의 시트/이름 정의가 존재하는지 확인하다.
10. 유지보수 표준 템플릿
10.1 환경 테이블 예시
| 항목 | 예시 값 | 비고 |
|---|---|---|
| Server | sqlprod01.company.local | 프로덕션 |
| DB | SalesDW | DW |
| ShareRoot | \\filesrv\team | UNC |
| Mode | Prod | Dev/Prod 구분 |
10.2 오류 보고 양식
프로젝트/파일: 증상: 소스 유형/경로: 마지막 정상 일시: 변경 내역(DB 스키마/파일/권한): 재현 절차: 스크린샷/로그: 요청 조치: 11. 운영상 베스트 프랙티스
- 쿼리·연결은 이름 규칙(Prefix_Source_Target_Purpose)으로 관리하다.
- 주요 변환은 단계 주석과 버전 태그를 남기다.
- 대용량 CSV는 인코딩·구분자·헤더 유무를 명시하다.
- 새로 고침 스케줄은 업무 마감 전 버퍼를 두고 분산하다.
12. 포렌식: 실패 원인 재현과 고립
- 동일 쿼리를 새 통합문서에서 재현하여 파일 손상 여부를 배제하다.
- 쿼리 단계를 이분 탐색으로 줄여 실패 지점을 고립하다.
- 소스를 CSV 스냅샷으로 임시 대체하여 변환 단계의 건전성을 검증하다.
13. 네트워크 환경별 특이사항
| 환경 | 특이점 | 대응 |
|---|---|---|
| 프록시 필수 | 웹 커넥터 인증 루프 | 프록시 예외에 소스 도메인 추가 |
| VDI/원격 | 드라이브 문자 상이 | UNC 고정, 사용자 프로필 경로 지양 |
| 망분리 | 외부 웹 차단 | 내부 미러·파일 스테이징 |
14. 복구 스크립트·자동화
14.1 PowerShell로 포트 점검 배치
param( [string]$HostName = "db.company.local", [int]$Port = 1433 ) Test-NetConnection $HostName -Port $Port | Format-List 14.2 VBA로 단계적 새로 고침과 로깅
Sub RefreshWithLog() Dim c As WorkbookConnection For Each c In ThisWorkbook.Connections On Error Resume Next c.Refresh Debug.Print Now & " - " & c.Name & " - " & c.OLEDBConnection.Connection On Error GoTo 0 Next c End Sub 주의 : 연결 문자열 노출에 유의하다. 로그는 보안이 확보된 위치에만 저장한다.
15. 최종 점검 체크리스트
- 신뢰 센터 설정 및 위치 확인을 완료하다.
- 자격 증명을 재설정하고 인증 방식을 표준화하다.
- 프라이버시 수준을 일관되게 구성하다.
- UNC/상대경로 전략으로 링크를 안정화하다.
- 드라이버·비트 수·TLS 정책을 일치시키다.
- 쿼리 폴딩 유지와 시간 제한 상향을 점검하다.
- 스키마 변경에 견고한 변환 패턴을 적용하다.
- 오류 로깅과 재현 절차를 문서화하다.
FAQ
Power Query “개인정보 수준 충돌”을 해제해도 될까?
업무 데이터와 공개 데이터를 결합할 필요가 없으면 소스별 수준을 “조직”으로 통일하여 해결하는 것을 권장한다. 전역 비활성화는 데이터 유출 위험을 키우므로 피한다.
외부 링크가 많은 파일이 느리다. 해결책은?
링크를 Power Query로 흡수하여 단일 새로 고침 파이프라인으로 통합하고, 값만 복제하는 스냅샷 시트를 두어 계산 부담을 줄이는 것이 효과적이다.
드라이버를 바꾼 뒤 일부 PC만 실패한다.
비트 수 불일치, 레거시 DSN 존재, TLS/인증서 스토어 차이를 확인한다. 가능한 DSN 없는 연결로 통일한다.
SharePoint 경로가 자꾸 바뀐다.
문서 라이브러리의 서버 상대 경로 또는 파일 ID 기반 URL을 사용하고, 최종 리디렉션 URL을 쿼리에 고정한다.
“열을 찾을 수 없음”을 예방하려면?
초기 단계에서 열 서브셋을 확정하지 말고, 존재 확인 후 형변환·이름변경을 조건부로 적용하는 패턴을 사용한다.