Error Connecting To SQL using VBA Code Without Using Windows Authentication

Question

I am trying to connect to a SQL via VBA Code on Excel.

The problem is that when I am logged in on my username, I can connect to the database without problem. But when I try to connect with other user logged in, the connection is unsucessful.

I have already created a user inside my Database, like the picture below:

And my connection on VBA is being done this way:

Option Explicit

Public DB As New ADODB.Connection
Public RST As New ADODB.Recordset
Public caminho, strConn As String
Public Msg As String

Sub CONECTA_DB()

strConn = "Provider=SQLOLEDB;Data Source=BR1104053\SQLEXPRESS;" & _
          "Initial Catalog=BDVestiario;" & _
          "Integrated Security=SSPI;" & _
          "User ID=bdvestiario;" & _
          "Password=#infra#sudeste#;"


Set DB = New ADODB.Connection
Set RST = New ADODB.Recordset

DB.Open strConn

RST.CursorLocation = adUseClient

This gives a connection error when logged on other user, but connects normally when I am loggen on my user.

The problem, is that I need all my company to use this excel sheet connecting to SQL, so I need a common username to read and write on the database.

It probably doesn't really matter in this case (or does it?), but I'd strongly recommend you redact passwords/credentials when you post anything online. Feel free to edit it out, and note you can flag it for moderator attention, to get the revision history redacted, if you need to (same with the answer you got) — Mathieu Guindon
– Mathieu Guindon, Commented Apr 13, 2017 at 20:14

jradich1234 · Accepted Answer · 2017-04-13 20:02:41Z

2

You need to set Integrated Security to false or removed completely from your connection string. When this is true or SSIS Windows credentials are used.

strConn = "Provider=SQLOLEDB;Data Source=BR1104053\SQLEXPRESS;" & _
      "Initial Catalog=BDVestiario;" & _
      "User ID=bdvestiario;" & _
      "Password=#infra#sudeste#;"

answered Apr 13, 2017 at 20:02

jradich1234

1,4255 gold badges24 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pedrocsa Over a year ago

I have tried this, but still have no success. I received a failed logon message with the 'bdvestiario' user when I try this. Maybe I'm creating the user on the wrong way on SQL Server? @jradich1234

jradich1234 Over a year ago

I just noticed that you're using SQL Express. Have you configured the SQL Express server to allow remote connections? It does not allow them by default. Here's a link on how to enable remote connections.

pedrocsa Over a year ago

thanks @jradich1234...I have already done this too...but I discovered the problem. I didn't restart the server after created the new logon. After the restart it worked perfectly! Thanks!

Collectives™ on Stack Overflow

Error Connecting To SQL using VBA Code Without Using Windows Authentication

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related