当前位置：首页 > news >正文

Sqlserver安全篇之_Sqlcmd命令使用windows域账号认证sqlserver遇到问题如何处理的案例

news 2025/8/16 22:44:14

sqlcmd
https://learn.microsoft.com/zh-cn/sql/tools/sqlcmd/sqlcmd-connect-database-engine?view=sql-server-ver16

sqlcmd -S指定的数据库连接字符串必须有对应的有效的SPN信息，否则会报错SSPI Provider: Server not found in Kerberos database.

正常连接
1、tcp:hostname,port

/opt/mssql-tools18/bin/sqlcmd -S tcp:dbdev1,1433 -N true -C -Q "select top 1 name from sys.databases"
/opt/mssql-tools18/bin/sqlcmd -S tcp:stagingdb1,63061 -N true -C -Q "select top 1 name from sys.databases"

2、hostname

/opt/mssql-tools18/bin/sqlcmd -S dbdev1 -N true -C -Q "select top 1 name from sys.databases"

3、hostname\instance

/opt/mssql-tools18/bin/sqlcmd -S "stagingdb1\db62lm" -N true -C -Q "select top 1 name from sys.databases"

4、hostname,port

/opt/mssql-tools18/bin/sqlcmd -S dbdev1,1433 -N true -C -Q "select top 1 name from sys.databases"
/opt/mssql-tools18/bin/sqlcmd -S stagingdb1,63061 -N true -C -Q "select top 1 name from sys.databases"

无法正常连接，这是因为SPN里面的信息只有服务器dbdev1或stagingdb1的名称，而没有ip的名称
1、tcp:ip,port

/opt/mssql-tools18/bin/sqlcmd -S tcp:172.22.137.251,1433 -N true -C -Q "select top 1 name from sys.databases"
/opt/mssql-tools18/bin/sqlcmd -S tcp:10.0.2.195,63061 -N true -C -Q "select top 1 name from sys.databases"
报错
SSPI Provider: Server not found in Kerberos database.
Cannot generate SSPI context.

2、ip

/opt/mssql-tools18/bin/sqlcmd -S 172.22.137.251 -N true -C -Q "select top 1 name from sys.databases"
报错
SSPI Provider: Server not found in Kerberos database.
Cannot generate SSPI context.

3、ip\instance

/opt/mssql-tools18/bin/sqlcmd -S "10.0.2.195\db62lm" -N true -C -Q "select top 1 name from sys.databases"
报错
SSPI Provider: Server not found in Kerberos database.
Cannot generate SSPI context.

4、ip,port

/opt/mssql-tools18/bin/sqlcmd -S 172.22.137.251,1433 -N true -C -Q "select top 1 name from sys.databases"
/opt/mssql-tools18/bin/sqlcmd -S 10.0.2.195,63061 -N true -C -Q "select top 1 name from sys.databases"
报错
SSPI Provider: Server not found in Kerberos database.
Cannot generate SSPI context.

sqlcmd遇到过的问题，windows域账号的kerberos ticket正常的情况下，windows域账号认证sqlserver报错

sqlcmd报错

webprocess@paystaget1:~$ /opt/mssql-tools18/bin/sqlcmd -S 172.22.137.251,1433 -N true -C -Q "select top 1 name from sys.databases"
Sqlcmd:Error: Microsoft ODBc priver 18 for SQLLServor: SSPI Provider: No Credentials were supplied,or the Credentials were unavailable or inaccessible. No Kerberos credentials available(defaul cache: File:/tmp/krb5cc_946009658)
Sqlcmd: Error: Microsoft ODBc Driver 18 for SQLServer:Can not generate SSPI context

分析
1、该域账号的kerberos ticket

root@paystaget1:~# klist /opt/apache-tomcat-9.0.62/conf/krb5cc_genticket
Ticket cache: FILE:/opt/apache-tomcat-9.0.62/conf/krb5cc_genticket
Default principal: webprocess@DAI.NETDAI.COMValid starting     Expires            Service principal
04/17/25 16:00:03  04/18/25 02:00:03  krbtgt/DAI.NETDAI.COM@DAI.NETDAI.COMrenew until 04/18/25 16:00:03

2、域账号不会直接使用kerberos ticket文件，而是根据该域账号对应的uid，默认使用/tmp/krb5cc_uid

root@paystaget1:~# chown webprocess.webprocess /tmp/krb5cc_946009658
报错chown:invalid user: `webprocess.webprocess`

3、自此发现这种域账号webprocess，只有uid是webprocess但是没有gid是webprocess，所以chown时无法使用webprocess.webprocess这样的方式写死域用户和域用户组

root@paystaget1:~# id webprocess
uid=946009658(webprocess) gid=946000513(domain users) groups=946000513(domain users),946040890(sophosuser),946063848(alkgo user),946071842(sso quiver)

解决方法

root@paystaget1:~# cp /opt/apache-tomcat-9.0.62/conf/krb5cc_genticket /tmp/krb5cc_946009658
root@paystaget1:~# chown webprocess /tmp/krb5cc_946009658
root@paystaget1:~# klist /tmp/krb5cc_946009658
webprocess@paystaget1:/root$ /opt/mssql-tools18/bin/sqlcmd -S tcp:dbdev1,1433 -N true -C -Q "select top 1 name from sys.databases"
name
--------------------------------------------------------------------------------------------------------------------------------
master(1 rows affected)