Access Front-end can't connect to Sql Server (1 Viewer)

[jlabre01]

Hi,

I just recently migrated my Access 2003 database over to MS SQL Server 2005 using the Migration assistant provided by MS. However at my business we have multiple workstations using this and when I try to use the database (through the access front-end) we get an error message saying that there was an error connecting to the server. error code was 18452. please help...

jlabre01

 

[SQL_Hell]

Have you added the users to sql server?

What type of authentication are you using? windows or sql server?

What does the sql server error log say? you can get this by opening a new query in management studio and typing 'exec xp_readerrorlog'

 

[MSAccessRookie]

How are you connecting to the SQL Server? There are two primary ways to do so.

1. Connect via Windows User ID. You will need to set up an SQL Server User ID for each Windows User ID.
2. Connect via Trusted User Mode. No password is given, and there are security and ID issues.

Check the available privileges and rights for the User ID, because Error Code 17055 could be interpreted as "User ID is (null) or not associated with a trusted account"

 

[jlabre01]

I have added the 2 users necesary to test whether this is working or not to the sql server using the management studio.
It's using windows authentication.
The error log just says the the username is not associated with a trusted connection

 

[SQL_Hell]

How are you connecting to the SQL Server? There are two primary ways to do so.

1. Connect via Windows User ID. You will need to set up an SQL Server User ID for each Windows User ID.
2. Connect via Trusted User Mode. No password is given, and there are security and ID issues.

Check the available privileges and rights for the User ID, because Error Code 17055 could be interpreted as "User ID is (null) or not associated with a trusted account"

sorry mate, I guess we were typing at the same time

 

[SQL_Hell]

and your definitely logged on with one of these users on the client machine?

in management studio can you execute the following 2 queries for me and show the output?

use master

select name,* from sys.syslogins

use *your database name here*

select name,* from sys.sysusers

 

[MSAccessRookie]

sorry mate, I guess we were typing at the same time

Not to worry, It happens all the time. It was interesting that although we had similar ideas, we each added something for him to consider. I think the Trusted Account message indicates some type of security issue, and he should examine the SQL Server IDs for the users that have been set up.

 

[jlabre01]

We connect using the first method. like I said earlier, we go to open the forms in access, and it says that we aren't connected, then a popup shows up asking to login and using the trusted connection, it doesn't connect and when we try to deselect the trusted connection and enter the windows id and password it doesn't work either. even though the user id was added to the logins section of the security on the server management studio.

 

[SQL_Hell]

There are two parts to sql server security, logins and users.

what does this give you?

use *your database name here*

select name,* from sys.sysusers

 

[jlabre01]

when I entered the queries in nothing came up at all

 

[SQL_Hell]

I assume you are replacing *your database name here* with your database name?

even if the queries showed no records then you would still get the column names in the query output, do you get the column names?

Also go to management studio and double click on the login you added and select 'user mapping' ...... does the login have access to the database?

 

[MSAccessRookie]

when I entered the queries in nothing came up at all

Are you looking in your own database as opposed to another (did you say "Use {YourDataBase Name}"? If you are, then it sounds like your SQL Server IDs are not set up properly.

Also, are you intending to use ODBC? I had similar messages until I had my ODBC Drivers defined properly.

 

[jlabre01]

I opened a new query and typed use mydatabase, then select name,* from sys.sysusers
nothing happened, the cursor just went to the next line. no column names, and yes the login i added has access to the database.

how would i go about configuring the odbc drivers?

 

[jlabre01]

i made a modification to the server made it check both sql and windows authentications, now when we try to login with the trusted connection check-box checked it says sql state S1000 and the error is 18456. does that help at all?

 

[MSAccessRookie]

i made a modification to the server made it check both sql and windows authentications, now when we try to login with the trusted connection check-box checked it says sql state S1000 and the error is 18456. does that help at all?

SQL State S1000 can be interpreted as "Unable to Connect to Data Source". This could be due to an Undefined ODBC Source, or a source with limited connections that is currently busy on all available connections. From what you are describing, the former (Undefined ODBC Source) is more likely.

 

[SQL_Hell]

I opened a new query and typed use mydatabase, then select name,* from sys.sysusers
nothing happened, the cursor just went to the next line. no column names, and yes the login i added has access to the database.

how would i go about configuring the odbc drivers?

You have to actually execute the query by pressing the execute button at the top!

But seeing as you have created a login and a user....not just a login then it's irrelevant.

I would go with what MSAccessRookie says and check ODBC drivers, stick the sql server 2005 disk in your client machine and install "client connectivity". The ODBC driver you are looking for is called "sql server native client" and the version should start with 2005.

 

[jlabre01]

so, what would you recommend I do to fix this problem?

 

[MSAccessRookie]

so, what would you recommend I do to fix this problem?

Check to see if you have an ODBC Driver set up for your project. One easy way to do that is to go to the Database Window of your Access Project, and view the tables. If you have an ODBC Link, the table Icons will have a small arrow pointing at a blue/green ball (resembling the planet Earth?).

Right clicking on one of the tables will provide a menu with an option titled "Linked Table Manager". Selecting that option will open a window displaying the table links. If they are ODBC, entries will look like :

{TableName} ( DSN={ODBC DSN Name};, DATABASE={YourDatabaseName}; )

Tell us what yours says

 

[jlabre01]

it says: tablename (Database = lab projects)

 

[MSAccessRookie]

it says: tablename (Database = lab projects)

Is "Lab Projects" the name of the Database located on your SQL Server? That sounds odd to me since SQL Server does not like any spaces in the names of its objects.