SQL Server 2005 Login

[trackdaychamp]

Hi,

I am putting a SQL Server Backend on an Access frontend.

When I create a new Login in SQL Server 2005 Enterprise Edition, I assign it a username and password. However when I go to view the properties of the login I just created there is a problem.

Instead of seeing the 4 character password I entered, I am seeing a 15 character password that is masked. Does anyone know why this happens?

This is causing problems for me connecting to the SQL Server DB

Thanks,

Mark

 

[SQL_Hell]

The password is in encrypted binary, so the amount of ***** shown does not relate to the amount of characters in your password.

However this should not stop you connecting, provided you type the correct password you should be able to connect.

Also please check you have no password expiry enabled (which is the default)

 

[trackdaychamp]

Hi SQL Hell

I have deselected the password expiry option

This is my connection string but it says Login Failed for MyUser. The user is not associated with a trusted SQL Server Connection.

Here is my code:

Dim cn as new ADODB.Connection
Set cn = New ADODB.Connection

Dim strQuery as String
StrQuery = "Provider=sqloledb;Server=MyServer;Database=SQLServerDB"
cn.open strQuery, "SQL login", "SQL password"

 

[SQL_Hell]

Hi again,

I am not totally sure (long time since I have done this) but I think your connection string is wrong and the database server thinks you are using windows authentication instead of sql server authentication, please try this...

Dim cn as new ADODB.Connection
Set cn = New ADODB.Connection

Dim strQuery as String
StrQuery = "Provider=sqloledb;Data Source=myServer;Initial Catalog=SQLSer verDB;User Id=myUsername;Password=myPassword"
cn.open strQuery

 

[trackdaychamp]

Hell indeed!

I checked the connection string again and adjusted the syntax as advised.

It prompts me that "Login failed for user 'MyUser'. The user is not associated with trusted SQL Server connection."

I presume this points to the actually login entity on the SQL Server side? Even though I have set up the Login and Password correctly whilst also setting up the User Mapping.

Thanks,

Mark

 

[boblarson]

After creating the login within SQL Server, have you assigned permissions for that user name?

 

[trackdaychamp]

Bob,

I have mapped the login of the SQL Server User to the following Database role members:

• db_datareader
• db_datawriter

Thanks,

Mark

 

[SQL_Hell]

ok lets verify the sql server login,

open query analzer and connect to that server using the login name and password and check that works

 

[trackdaychamp]

I apologise for my ignorance but how do I do this?

I mean how do I attempt to connect through code in a SQL statement (query)?

Sorry,

Mark

 

[pdx_man]

Let me throw my .02 in here, if I may.

It prompts me that "Login failed for user 'MyUser'. The user is not associated with trusted SQL Server connection."

As Hell pointed out, you are still using NT Authnetication. Look at you connection string. Anywhere in the do you see a:
Trusted Connection=Yes
if so, get rid of it.

Check the authentication mode of your SQL Server. Is it in Mixed Mode allowing both SQL and NT Authentication? If not, change it to allow both.

For the newly created user, what is the default database? Do they have rights to that database?

These are the 3 things I can think of that would cause this type of error. If this doesn't solve your problem, post your conection string exactly as it is, then go change the password to something else.

 

[trackdaychamp]

Pdx Man,

Thanks for your input


1 - I have attached the modified query string in full below.
2 - Where in SQL do I check if mixed authentication is allowed / enabled?
3 - The user has db.owner rights to all the tables in the DB, so that should be fine.

I guess its just the question I have in point 2 above...

Thanks,

Mark

'Open the connection to the SQL Server DB
Set cn = New ADODB.Connection
strQuery = "Provider='sqloledb';Data Source='Deloitte-A583B4';" & _
"Initial Catalog='PMO_SQLSERVER_DB';User Id='MyLogin';Password='123456'"
cn.Open strQuery

 

[pdx_man]

cn.connectionString = strQuery

You need to set the ConectionString property.