sql server数据迁移，springboot搭建开发环境遇到的问题及解决方案

最近搭建springboot项目开发环境，数据库连的是sql server，遇到许多问题在此记录一下。

1、sql server安装教程

参考：https://www.bilibili.com/opus/944736210624970769

2、sql server导出、导入数据库

参考：https://blog.csdn.net/noriyyds/article/details/130814064

3、用Windows身份验证方式登录SQL Server Management Studio添加一个新的用户并设置密码，密码要足够复杂才可。

– 给用户分配角色
CREATE USER userName FOR LOGIN userName ;
ALTER ROLE db_datareader ADD MEMBER userName;
ALTER ROLE db_datawriter ADD MEMBER userName;

4、application.yml配置

spring:

  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
    druid:
      master:
         url: jdbc:sqlserver://XXX.XXX.XXX.XXX:1433;databaseName=nrid;sendStringParametersAsUnicode=true;encrypt=true;trustServerCertificate=true
         username: userName
         password: userPassWord

5、运行报错和解决办法

5.1后台报错：com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host XXX.XXX.XXX.XXX, port 1433 has failed. Error: “Connection timed out: no further information… Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”.

解决方法：https://blog.csdn.net/weixin_47246285/article/details/130605313

5.2后台报错： Error querying database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The SELECT permission was denied on the object ‘sys_job’, database ‘nrid’, schema ‘dbo’.

解决方法：用Windows身份验证方式登录SQL Server Management Studio执行语句
USE [YourDatabaseName];
GO
GRANT SELECT ON SCHEMA::dbo TO [YourUserName];
GO
然后把insert、update、delete权限都赋给新添加的用户。