tags:

views:

16

answers:

2
Q: 

sql server 2008: setting up a username properly in order to connect from VBA

i set up a login (reviewer) under security for SQL SERVER

and under the specific database i set up a User (qcvalues_reviewer) that references this login

i have tried all these different ways to login into the database from vba:

 'cn.Open "Provider=SQLOLEDB;Data Source=hermes;Initial Catalog=test;User ID=qcvalues_reviewer; Password=92127; Trusted_Connection=yes"

 'cn.Open "Provider=SQLOLEDB;Server=hermes;Database=test;User Id=qcvalues_reviewer;Password=92127;"

 'cn.Open "Provider=SQLOLEDB;Data Source=hermes;Initial Catalog=test;User Id=qcvalues_reviewer;Password=92127;"

 'cn.Open "Provider=SQLOLEDB;Data Source=10.20.10.18,1433;Network Library=DBMSSOCN;Initial Catalog=test;User ID=qcvalues_reviewer;Password=92127;"

each time i get the error message:

login failed for user

can someone please help me connect!

+1  A: 
Chris Diver  2010-08-19 18:14:15
using sql server manamgen studio, how do i enable remote connections?
I__  2010-08-19 18:15:22
Management studio is a tool you use to connect to SQL server, I mean the `edition` of SQL Server you are using, you can find this out with `SELECT @@VERSION`. Here is a how to if you are using that edition http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/
Chris Diver  2010-08-19 18:19:16
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)
I__  2010-08-19 18:23:47
As @GSerg points out you should log in with the LOGIN and not the USER, I read that wrong. - im sorry i dont understand can you write that for me
I__  2010-08-19 18:24:50
That's standard edition - it will have remote connections enabled by default - your problem will be the `User Id` see @GSerg's post.
Chris Diver  2010-08-19 18:25:37
thank you guys, i have tried the new string that chris provided and it still says bad login
I__  2010-08-19 18:27:22
should the initial catalog be the database which i want to see?
I__  2010-08-19 18:29:39
What is the exact error message, or is that it?
Chris Diver  2010-08-19 18:31:02
login failed for user reviewer
I__  2010-08-19 18:32:09
Did you set the `reviewer` `LOGIN` in windows (as a windows user) or did you set the user/password in Management Studio (as a SQL user)?
Chris Diver  2010-08-19 18:41:52
i set the pass in managemen studio
I__  2010-08-19 18:43:09
In the SQL error log see if it has a message about the failed login and it should give you a `state` code
Chris Diver  2010-08-19 18:46:09
2010-08-19 11:28:33.25 Logon Login failed for user 'reviewer'. Reason: Password did not match that for the login provided. [CLIENT: 10.0.0.204]2010-08-19 11:29:04.84 Logon Error: 18456, Severity: 14, State: 8.2010-08-19 11:29:04.84 Logon Login failed for user 'reviewer'. Reason: Password did not match that for the login provided. [CLIENT: 10.0.0.204]2010-08-19 11:31:27.80 Logon Error: 18456, Severity: 14, State: 8.2010-08-19 11:31:27.80 Logon Login failed for user 'reviewer'. Reason: Password did not match that for the login provided. [CLIENT: 10.0.0.204]
I__  2010-08-19 18:49:47
There is your answer - the password is incorrect. Reset the password on the login - or change it in the connection string if there is a typo.
Chris Diver  2010-08-19 18:50:46
i had the wrong password :) thank you so so so so so so much
I__  2010-08-19 18:50:53
+1  A: 

You should be using the login (reviewer), not the user name (qcvalues_reviewer).

Also try using the second and the third parameters of Connection.Open for User and Login, respectively.

GSerg  2010-08-19 18:17:40
im sorry i dont understand what you mean?
I__  2010-08-19 18:18:34
You're trying to have User ID and password in the connection string, and I'm saying, try the parameters of the Open method for that. In either case, you should be using the login name, not the user name.
GSerg  2010-08-19 18:24:11
can you show me what that string would look like please
I__  2010-08-19 18:25:50
thank you guys, i have tried the new string that chris provided and it still says bad login
I__  2010-08-19 18:27:39
cn.Open "Provider=SQLOLEDB;Data Source=hermes;Initial Catalog=test", "reviewer", "92127"
GSerg  2010-08-19 18:29:44
invlaid authroization sepcification
I__  2010-08-19 18:31:40
Have you enabled mixed mode authorization on the server, or is it only willing to accept trusted connections?
GSerg  2010-08-19 18:39:16
how do i enable mixed mode?
I__  2010-08-19 18:39:38
GSerg  2010-08-19 18:43:20
yes i checked and both modes are available
I__  2010-08-19 18:46:58
i had the wrong password :) thank you so so so so so so much
I__  2010-08-19 18:51:22

related questions

Sql to query a dbs scheme
Transform Columns into Rows
SQL Client for Mac OS X that works with MS SQL Server
How do you get leading wildcard full-text searches to work in SQL Server?
SQL Server 2000: Is there a way to tell when a record was last modified?
What's the BEST way to remove the time portion of a datetime value (SQL Server)
I need to know how much disk space a table is using in SQL Server
What's the best way to determine if a temporary table exists in SQL Server?
Appropriate pagefile size for SQL Server
Have you ever encountered a query that SQL Server could not execute because it referenced too many tables?
ASP.NET MVC "CRUD" Database Sample
How do I unit test persistence?
Best Book for a new Database Developer
Can I logically reorder columns in a table?
Best way to copy a database in SQL Server 2005/8?
Is Windows Server 2008 "Server Core" appropriate for a SQL Server instance?
Why doesn't SQL Full Text Indexing return results for words containing #?
Client collation and SQL Server 2005
Editing database records by multiple users
Deploying SQL Server Databases from Test to Live
SQL Server 2005 implementation of MySQL REPLACE INTO?
Upgrading SQL Server 6.5
How do I version my MS SQL database in SVN?
How to export data from SQL Server 2005 to MySQL
Check for changes to a SQL table?