VBA / EXCEL - Import data from MS SQL server, access denied

Hi guys,

I'm been working a vba code that would connects to a MS SQL server and import data to Excel, but i'm having some issues with it...

I'm getting this error:
vba_error.jpg
I have checked up in MSSMS the roles etc. and everything seems to look fine, It works fine on my comp, since i'm added as DB_Owner with my AD account, athough I want to user another account for the dataimport which is a local user, this should not be a problem I guess? Maybe i'm using a wrong provider?

I'm fairly new in VBA so just really trying to learn :)

Please have a look at the sample file

thank you in advance
samplefile.xlsm
LVL 1
HakumAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HakumAuthor Commented:
Extra note: the database has been changed from the screenshot to the code in the sample file
ste5anSenior DeveloperCommented:
First of all: I strongly recommend to install SQL Server Native Client. Here's the actual version included in the SQL Server 2012 Feature Pack..

See also Installing SQL Server Native Client.

As you said, you're now using a local account: Have you create a login for it?
HakumAuthor Commented:
That will sadly be a problem, since its not possible for us to roll the native client out to all the users, but could you tell me why we should rollout the native driver?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ste5anSenior DeveloperCommented:
Better performance :)
HakumAuthor Commented:
hehehe okay, which might be the problem here.... ooorrrr? would this work aswell?

the provider I'm using here is this:

strConn = "Provider=MSDASQL.1;" 'DB Provider
strConn = strConn & "driver={SQL Server};"
strConn = strConn & "Server=APUTP001,10433;" ' Servernavn
strConn = strConn & "Database=Testdatabase;" ' Databasenavn
strConn = strConn & "uid:=" & strUserID & ";"
strConn = strConn & "pwd:=" & strPassword
Vitor MontalvãoMSSQL Senior EngineerCommented:
The error message is "Login Failed" so you need to verify if the user (strUserID) exists in the MSSQL instance (APUTP001) and in the database (Testdatabase). If afirmative then you need to check if you provided the correct password (strPassword)
HakumAuthor Commented:
Thank you Vitor - I have tested that and i have createt the user from scratch various times since i thought that was the issue aswell.. but no luck :( so in other word i have checked up on that
Vitor MontalvãoMSSQL Senior EngineerCommented:
I have tested that and i have createt the user from scratch
Can you show how you created the user?
HakumAuthor Commented:
sure...

i did it like this:

https://www.youtube.com/watch?v=3Lz9rFVRRfU
YZlatCommented:
Which version of SQL Server are you using? Also are there multiple databases?

P.S. It is possible you created user in one database and trying to access a different database where this user does not exist
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, I mean, can you script the user and post it here?
Don't forget to replace sensitive data with dummy data.
HakumAuthor Commented:
This is what we are running:

sqlserver.JPG
And no i tripled checked that the users is mapped to the correct database and when i look in security under that particular database the user is there.
HakumAuthor Commented:
@Vitor:
Sorry i'm not that good at SQL either but I guess this is what you needed?

sqluser.JPG
HakumAuthor Commented:
any one?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry Hakum, I just ran out of ideas.
If something comes in my mind I will back to this question to help you.
YZlatCommented:
I think the problem is in your connection string. uid and pwd are just incorrect when trying to connect to SQL Server. I believe those are used to connect to Access

try this:

strConn="Provider=MSDASQL.1;Server=APUTP001,10433;Database=Testdatabase;User Id=" & strUserID & ";Password=" & strPassword & ";"

Open in new window


If does not work, change your provider to SQLOLEDB:

strConn="Provider=SQLOLEDB;Server=APUTP001,10433;Database=Testdatabase;User Id=" & strUserID & ";Password=" & strPassword & ";"

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HakumAuthor Commented:
AWESOME! got it working! I tweaked the VBA code abit, after looking into the connection string.

Thank you soo much all for your time and energy used!!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.