Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
strCnn = "Provider=SQLOLEDB;Server=ServerNT;" & _
"Database=Pubs;uid=sa;pwd=;"
cnn1.Open strCnn //用户‘sa’登陆失败,原因:未与信任SQL Server连接相关联,这是什么错误?如何解决?
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
strCnn = "Provider=SQLOLEDB;Server=ServerNT;" & _
"Database=Pubs;uid=sa;pwd=;"
cnn1.Open strCnn //用户‘sa’登陆失败,原因:未与信任SQL Server连接相关联,这是什么错误?如何解决?
如果采用namepipe,最好客户端同SQL Server在同一个Windows Domain中。
你也可以这样做,先访问SQL Server机器,在文件浏览器中\\[SQLServer的机器名]
如果提示用户名和密码,就输入,只要登陆没有问题。你上面的问题就解决了
ServerNT是我本机,我安装了SQL Server2000,并启动了SQL Server 服务,数据库Pubs是本机上的SQL Server管理的一个数据库例子,安装SQL Server的时候就有的,登陆名sa,密码没有。
就可以登陆,不知道这两个安全性的属性作何解?
验证、信息完整性、信息隐私等集成安全服务,以及用于所有分布式应用
程序协议的安全方面的服务
使用Windows NT集成安全设置 登陆,得到如下连接字符串
strCnn = "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=ServerNT"
可以登陆并访问数据库如果使用指定的用户名称和密码,我输入用户名和密码就不能登陆,不知道指定的用户名和密码在哪里可以修改,我记得我安装SQL Server的时候,它提示输入登陆的用户名和密码,可能我当时输错了,忘记了指定的用户名和密码,现在想知道如何修改?
(在用户名和密码正确情况下)
ConnectionString = ”Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=ServerNT“
constring = "driver={SQL Server};server=ServerNT;uid=sa;pwd=;database=Pubs"
cnn1.Open constring
constring = "driver={SQL Server};server=127.0.0.1;uid=sa;pwd=;database=Pubs"
cnn1.Open constring
SQL Server在安装的时候,有两种方式,一种是Windows NT 验证、一种是SQL Server验证,如果你选择了前一个(从你的情况看,是这种情况),就用集成验证的连接,否则,用后面!!
看看下面的程序,你就会明白二者的区别了!!
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'连接字符串参数
'
'
'要连接该提供者,将(ConnectionString 属性的参数)“Provider=”设置为:
'
'MSDASQL
'
'读取 Provider 属性也将返回该字符串。
'
'由于这是 ADO 的默认提供者,所以如果省略连接字符串的 Provider= 参数,ADO 将试图建立与该提供者的连接。
'
'除了 ADO 所定义的参数外,提供者不支持任何特定连接参数。但是,提供者将把任何非 ADO 连接参数传递给 ODBC 驱动程序管理器。
'
'由于可以省略 Provider 参数,因此使用与撰写 ODBC 连接字符串时用的相同参数名(DRIVER=、DATABASE=、DSN= 等等)、值和语法,可以撰写与同一数据源的 ODBC 连接字符串相同的 ADO 连接字符串。可以使用或不使用预定义的数据源名 (DSN) 或 FileDSN 进行连接。
'
'带有 DSN 或 FileDSN 的语法:
'
'"[Provider=MSDASQL;] { DSN=name | FileDSN=filename } ; [DATABASE=database;] UID=user; PWD=password"
'
'无 DSN(非 DSN 连接)的语法:
'
'"[Provider=MSDASQL;] DRIVER=driver; SERVER=server; DATABASE=database; UID=user; PWD=password"
'
'如果使用 DSN 或 FileDSN,则必须通过“Windows 控制面板”中的“ODBC 管理器”进行定义。作为设置 DSN 的替换方法,可以指定 ODBC 的驱动程序 (DRIVER=),诸如“SQLServer”、服务器名 (SERVER=) 和数据库名 (DATABASE=)。
'
'也可以在特定 ODBC 的参数或标准 ADO 定义的 User ID 和 Password 参数中为用户帐号 (PWD=) 指定用户帐号名 (UID=) 和密码。如果这些值中同时包括了 ADO 和特定 ODBC 的参数,则 ADO 参数优先。
'
'即使 DSN 定义已经指定了数据库,也可以在 DSN 之外指定 DATABASE 参数以便连接到不同的数据库。这同时更改了 DSN 定义以包括指定的数据库。使用 DSN 时始终包括 DATABASE 参数是一种好办法。这样将保证能连接到正确的数据库,因为其他用户可能会在上一次检查 DSN 定义后更改默认的数据库参数。 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim cn As New ADODB.Connection
Const Server = "SoftServer"
Const DBName = "pubs"
Const UID = "sa"
Const PWD = "123"
' DSN Connection using the OLE DB provider for ODBC - MSDASQL
Public Function ConnectSQLServerDSN(ByVal DBName As String, ByVal UserName As String, ByVal PWD As String) As Boolean
Dim strConnect As String On Error GoTo Error_ConnectSQLServerDSN
' DSN Connection using the OLE DB provider for ODBC - MSDASQL
strConnect = "DSN=;DATABASE=" & DBName & ";UID=" & UserName & ";PWD=" & PWD ' Prompt the user to select the DSN
cn.Properties("Prompt") = adPromptComplete
cn.Open ConnectSQLServerDSN = True
Exit FunctionError_ConnectSQLServerDSN:
ConnectSQLServerDSN = False
End Function
' DSNLess Connection using the OLE DB provider for ODBC - MSDASQL
Public Function ConnectSQLServer(ByVal ServerName As String, ByVal DBName As String, ByVal UserName As String, ByVal PWD As String, Optional ByVal ConnectionTimeOut As Integer = 15) As Boolean
Dim strConnect As String
On Error GoTo Error_ConnectSQLServer
' DSNLess Connection using the OLE DB provider for ODBC - MSDASQL
cn.CursorLocation = adUseClient
strConnect = "Driver={SQL Server};Server=" & ServerName & ";DataBase=" & DBName & ";UID=" & UserName & ";PWD=" & PWD & ";"
cn.ConnectionTimeOut = ConnectionTimeOut
cn.Open strConnect
ConnectSQLServer = True
Exit Function
Error_ConnectSQLServer:
ConnectSQLServer = False
End Function
' Connect using the OLE DB provider for SQL Server - SQLOLEDB
Public Function SQLOLEDBConnect(ByVal ServerName As String, ByVal DBName As String, ByVal UserName As String, ByVal PWD As String, Optional ByVal ConnectionTimeOut As Integer = 15) As Boolean Dim strConnect As String
On Error GoTo Error_SQLOLEDBConnect
' Connect using the OLE DB provider for SQL Server - SQLOLEDB
strConnect = "PROVIDER=SQLOLEDB;Server=" & ServerName & ";DataBase=" & DBName & ";UID=" & UserName & ";PWD=" & PWD & ";"
cn.ConnectionString = strConnect
cn.ConnectionTimeOut = ConnectionTimeOut
cn.Open SQLOLEDBConnect = True
Exit Function
Error_SQLOLEDBConnect:
SQLOLEDBConnect = False
End Function
' Connect using the OLE DB provider for SQL Server - SQLOLEDB(TrustConnect)
Public Function SQLOLEDBTrustedConnect(ByVal ServerName As String, ByVal DBName As String, bIntegratedSecurity As Boolean, Optional ByVal UserName As String, Optional ByVal PWD As String, Optional ByVal ConnectionTimeOut As Integer = 15) As Boolean Dim strConnect As String
On Error GoTo Error_SQLOLEDBTrustedConnect
' Connect using the OLE DB provider for SQL Server - SQLOLEDB(TrustConnect)
strConnect = "PROVIDER=SQLOLEDB;Server=" & ServerName & ";DataBase=" & DBName '& ";TRUSTED_CONNECTION=YES" '& ";UID=" & UserName & ";PWD=" & PWD & ";"
If bIntegratedSecurity = True Then
strConnect = strConnect & ";TRUSTED_CONNECTION=YES"
Else
' Otherwise supply the UserName and PWD
strConnect = strConnect & ";UID=" & UserName & ";PWD=" & PWD & ";"
End If cn.ConnectionString = strConnect
cn.ConnectionTimeOut = ConnectionTimeOut
cn.Open SQLOLEDBTrustedConnect = True
Exit Function
Error_SQLOLEDBTrustedConnect:
SQLOLEDBTrustedConnect = False
End Function
Public Function SQLOLEDBPropertiesConnect(ByVal ServerName As String, ByVal DBName As String, bIntegratedSecurity As Boolean, Optional ByVal UserName As String, Optional ByVal PWD As String) As Boolean Dim strConnect As String
On Error GoTo Error_SQLOLEDBPropertiesConnect
' Specify the OLE DB provider
cn.Provider = "sqloledb"
' Set the connection properties
cn.Properties("Data Source").Value = ServerName
cn.Properties("Initial Catalog").Value = DBName
' Check for Integrated security
If bIntegratedSecurity = True Then
cn.Properties("Integrated Security").Value = "SSPI"
Else
cn.Properties("User ID").Value = UserName
cn.Properties("Password").Value = PWD
End If
cn.Open SQLOLEDBPropertiesConnect = True
Exit Function
Error_SQLOLEDBPropertiesConnect:
SQLOLEDBPropertiesConnect = False
End Function
' Connect using the OLE DB provider for SQL Server - SQLOLEDB
Public Function SQLOLEDBUDLConnect() As Boolean Dim strConnect As String
On Error GoTo Error_SQLOLEDBUDLConnect
' Connect using the OLE DB provider for SQL Server - SQLOLEDB
strConnect = "File Name=" & App.Path & "\\udlSample.udl"
cn.ConnectionString = strConnect
cn.Open SQLOLEDBUDLConnect = True
Exit Function
Error_SQLOLEDBUDLConnect:
SQLOLEDBUDLConnect = False
End Function
'Use DataLinks
Public Function SQLOLEDBPromptConnect() As Boolean Dim dl As New MSDASC.DataLinks
On Error GoTo Error_SQLOLEDBPromptConnect
' Display the Data Link Dialog
Set cn = dl.PromptNew
On Error Resume Next
'Check object for nothing
If cn = "" Then
MsgBox "No connection information has been entered"
End
Else
cn.Open
End If
dl = Nothing
SQLOLEDBPromptConnect = True
Exit Function
Error_SQLOLEDBPromptConnect:
SQLOLEDBPromptConnect = False
End Function
'Test DSNLess Connect to SQL Server
Private Sub Command1_Click()
If ConnectSQLServer(Server, DBName, UID, PWD) Then
MsgBox "Success!"
Else
MsgBox "Failure!"
End If
If cn.State = adStateOpen Then cn.Close
Set cn = Nothing
End Sub
'Test DSN Connect to SQL Server
Private Sub Command2_Click()
If ConnectSQLServerDSN(DBName, UID, PWD) Then
MsgBox "Success!"
Else
MsgBox "Failure!"
End If
If cn.State = adStateOpen Then cn.Close
Set cn = NothingEnd Sub
Private Sub Command3_Click()
If SQLOLEDBConnect(Server, DBName, UID, PWD) Then
MsgBox "Success!"
Else
MsgBox "Failure!"
End If
If cn.State = adStateOpen Then cn.Close
Set cn = Nothing
End Sub
Private Sub Command4_Click()
'Or SQLOLEDBTrustedConnect(Server, DBName, True)
If SQLOLEDBTrustedConnect(Server, DBName, False, UID, PWD) Then
MsgBox "Success!"
Else
MsgBox "Failure!"
End If
If cn.State = adStateOpen Then cn.Close
Set cn = Nothing
End SubPrivate Sub Command5_Click()
'Or SQLOLEDBPropertiesConnect(Server, DBName, True)
If SQLOLEDBPropertiesConnect(Server, DBName, False, UID, PWD) Then
MsgBox "Success!"
Else
MsgBox "Failure!"
End If
If cn.State = adStateOpen Then cn.Close
Set cn = Nothing
End Sub
' Connect using the OLE DB provider for SQL Server - SQLOLEDB
Private Sub Command6_Click()
'Suppose the File udlSample.udl Exist,More Information Query SQL Server2000 Helper
If SQLOLEDBUDLConnect() Then
MsgBox "Success!"
Else
MsgBox "Failure!"
End If
If cn.State = adStateOpen Then cn.Close
Set cn = Nothing
End Sub
' Connect using DataLinks
Private Sub Command7_Click()
'Add the Reference of "Microsoft OLEDB Service Component1.0 Type Library"
If SQLOLEDBPromptConnect() Then
MsgBox "Success!"
Else
MsgBox "Failure!"
End If
If cn.State = adStateOpen Then cn.Close
Set cn = Nothing
End Sub
Public Function SQLOLEDBTrustedConnect(ByVal ServerName As String, ByVal DBName As String, bIntegratedSecurity As Boolean, Optional ByVal UserName As String, Optional ByVal PWD As String, Optional ByVal ConnectionTimeOut As Integer = 15) As Boolean Dim strConnect As String
On Error GoTo Error_SQLOLEDBTrustedConnect
' Connect using the OLE DB provider for SQL Server - SQLOLEDB(TrustConnect)
strConnect = "PROVIDER=SQLOLEDB;Server=" & ServerName & ";DataBase=" & DBName '& ";TRUSTED_CONNECTION=YES" '& ";UID=" & UserName & ";PWD=" & PWD & ";"
If bIntegratedSecurity = True Then
strConnect = strConnect & ";TRUSTED_CONNECTION=YES"
Else
' Otherwise supply the UserName and PWD
strConnect = strConnect & ";UID=" & UserName & ";PWD=" & PWD & ";"
End If cn.ConnectionString = strConnect
cn.ConnectionTimeOut = ConnectionTimeOut
cn.Open SQLOLEDBTrustedConnect = True
Exit Function
Error_SQLOLEDBTrustedConnect:
SQLOLEDBTrustedConnect = False
End Function