为何无法在SQL Server导入导出向导中使用SQL Server身份验证？

问题背景 #

使用SSMS 17的SQL Server导入导出向导时，SQL Server身份验证报错，提示Invalid authorization specification和Invalid connection string attribute (Microsoft SQL Server Native Client 11.0)；切换为Windows身份验证可正常连接，但无法看到需要导出的目标表，且已通过SQL Server身份验证登录数据库。

解决建议 #

• 修正连接字符串属性
  错误核心指向连接字符串参数异常，可按以下步骤调整：

  1. 在向导连接配置页点击「新建连接」，选择SQL Server Native Client 11.0作为提供程序
  2. 填写服务器名、SQL账号和密码后，切换到「全部」选项卡，删除多余或拼写错误的属性参数
  3. 也可直接手动输入标准格式的连接字符串：

     Server=你的服务器名称;Database=目标数据库名;Uid=SQL登录账号;Pwd=账号密码;Provider=SQLNCLI11.0;
     

• 验证SQL账号权限
  确保所用SQL账号同时具备：

  • 源数据库的SELECT读取权限
  • 目标数据库的VIEW DEFINITION（查看表结构）、INSERT/CREATE TABLE（写入/创建表）权限
    可在目标数据库执行以下语句赋予权限（替换对应账号和数据库名）：

  USE 目标数据库名;
  GRANT SELECT, VIEW DEFINITION, INSERT, CREATE TABLE TO [你的SQL账号];
  

• 更换数据提供程序
  尝试替换当前的SQL Server Native Client 11.0为Microsoft OLE DB Driver for SQL Server或SQL Server Native Client 10.0，部分版本的向导与Native Client 11.0存在兼容性问题。

• 确认服务器身份验证模式与版本兼容

  1. 登录目标SQL Server实例，在「服务器属性-安全性」中确认已启用「SQL Server和Windows身份验证模式」
  2. 检查SSMS 17与目标SQL Server版本的兼容性（SSMS 17适配SQL Server 2017，跨版本连接可能出现异常，建议匹配版本或升级SSMS）

错误详情 #

Invalid authorization specification
Invalid connection string attribute (Microsoft SQL Server Native Client 11.0)

------------------------------
Program Location:

   at System.Data.OleDb.OleDbConnectionNative..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at Microsoft.SqlServer.Dts.DtsWizard.DTSWizard.GetOpenedConnection(WizardInputs wizardInputs, String connEntryName)
   at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnLeavePage(LeavePageEventArgs e)

内容的提问来源于stack exchange，提问作者Vagner Hughs