从Excel导出到SQL:用户登录失败

I'm trying to create a function within Excel that will write data to my remote SQL Server database with a click of a button. However, I keep getting an error telling me that that my login failed for user "UserName" (Error 80040e4d).

我正在尝试在Excel中创建一个函数，它可以通过单击一个按钮将数据写入我的远程SQL Server数据库。然而，我不断收到一个错误信息，告诉我我的用户“UserName”登录失败(错误80040e4d)。

At first I thought there must be a problem with my User/Login within the database, so I went to the remote desktop, opened SQL Server Management Studio and checked all my permissions, made sure Windows authentication was selected, checked the password etc... Still didn't work.

一开始我认为我的用户/登录在数据库中一定有问题，所以我去了远程桌面，打开SQL Server Management Studio，检查了我所有的权限，确定了Windows认证，检查了密码等等……仍然没有工作。

My User has been given Read and Write permissions, so I tried to import data into Excel from SQL, which did work. So I'm assuming this must be an issue within my VBA code, more specifically, my connection string.

我的用户已经获得了读和写权限，所以我尝试从SQL中将数据导入Excel，这确实有效。我假设这一定是我的VBA代码中的问题，更具体地说，是我的连接字符串。

I would appreciate it if someone could take a look over it, and tell me what I'm doing wrong? Or if someone else has had this issue and knows what's causing it?

如果有人能看一下，告诉我我做错了什么，我会很感激的。或者如果其他人有这个问题知道是什么引起的?

Here's the code I have:

这是我的代码:

Sub Button3_Click()

Dim conn As New ADODB.Connection
Dim iRowNo As Integer
Dim sArtistId, sForename, sSurname, sNationality, sBirthDate, sDeathDate As String

With Sheets("NewArtist")

    'Open a connection to SQL Server
    conn.Open "Provider=SQLOLEDB;Data Source=tcp:IPADDRESS,1433\SqlServer2008;" & _
              "Initial Catalog=DatabaseName;User ID=UserName;Password=PassWord" & _
              "Integrated Security=SSPI;"

    'Skip the header row
    iRowNo = 2

    'Loop until empty cell in CustomerId
    Do Until .Cells(iRowNo, 1) = ""
        sCustomerId = .Cells(iRowNo, 1)
        sFirstName = .Cells(iRowNo, 2)
        sLastName = .Cells(iRowNo, 3)

        'Generate and execute sql statement to import the excel rows to SQL Server table
        conn.Execute "insert into dbo.Components (ArtistId, Forename, Surname, Nationality, BirthDate, DeathDate) values ('" & sArtistId & "', '" & sForename & "', '" & sSurname & "', '" & sNationality & "', '" & sBirthDate & "', '" & DeathDate & "')"

        iRowNo = iRowNo + 1
    Loop

    MsgBox "Artists imported."

    conn.Close
    Set conn = Nothing

End With

End Sub

Thank you very much!

非常感谢!

太瓦

1 个解决方案

#1

Integrated Security=SSPI

集成安全= SSPI

this parameter in your connection string means you are going to pass your windows principal name as authentication, so the sql login and password are being ignored.

连接字符串中的这个参数意味着您将把windows主体名作为身份验证传递，因此sql登录和密码将被忽略。

you should remove this name/value pair from the connection string, if you want to use a specified username and password.

如果要使用指定的用户名和密码，应该从连接字符串中删除这个名称/值对。

#1