Help getting ODCB working on Wiin7 client to SQLEXPRESS on server

I have 2 Win7 client PCs.  One that I setup a year ago and is running correctly.  I am an administrator on all equipment and using a member of a group on the 2nd PC.  

I can not get the 2nd PC to work.  My server AP is an Access 2007 accde, using SQLEXPRESS with Trusted Server Logon.

I started off by creating the ODCB using the 64 bit software (I got by doing a ODCB search) and when it did not work I researched to find I should have used the SYSWOW64 odbccad32.exe .  I admit to being confused about the proper way to tell that exe what I needed and have fumbled through many combinations trying to create the ODCB and nothing I've tried will advise the connection is OK.

I suspicion the Server thinks I am a "Guest" and that is why it is not working (maybe).  What networking permission might I have overlooked?  Must I be an administrator on the PC and the Server when creating the ODCB?  It seems I have tried every combination but nothing is creating an ODCB that works.  Is there a way to find out if I created a faulty OCDB that needs to be removed?  How do you delete an ODCB?

Your help most gratefully appreciated....
Who is Participating?
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.

Lionel MMSmall Business IT ConsultantCommented:
I have found that using the 64 ODCB is problematic--try the 32 bit version; when you get to the end of the setup and try "test connection" is it connecting successfully? And what user are you using when creating this ODCB, is it the same user you used when you created your database in SQL Express; are you using sa or Windows user authentication to logon to SQL EXpress?
Are you sure that your SQL database is listening on the standard port, 1433?  If not, it's a bugger to get ODBC to use another port.  

You would think that you'd set up the port using the IP address, colon, and then the port number, but that's not the case.  You must use a comma with no space.  Learned this the hard way recently!

Double check the firewall on the new server as it may be blocking 1433 or perhaps another port if it has been modified.

Also, depending on the requirements, you may need to set this up as a file, system, or user DSN.  User DSN is trickier in a multi-user scenario.  I ended up injecting the user DSN info into the user registry (profile) via a batch file.

So far as deleting extraneous ODBC configs, do a search for the odbc.ini folder in the registry.  You will likely find multiple entries under HKLM\Software and/or HKCU and/or even HKU.  Cautiously delete any that you don't feel should be retained.
Hypercat (Deb)Commented:
If the workstation is 64-bit, then you must use the odbcad32.exe file from the SYSWOW64 folder, not the standard ODBC setup file from the Windows\system32 folder.  If the workstation is 32-bit, then you must use the standard ODBC setup file which can be run by going to Control Panel\Administrative Tools\Data Sources (ODBC). If you don't use the correct odbcad32.exe setup program, then the connection will not work.

I advise logging on as a local administrator on the workstation and then set up the ODBC connection as a System DSN rather than a user DSN.  This guarantees that once you have it set up and working, the user can't change anything.

During the setup of the ODBC connection you should be able to test the connection to the server.  If the setup doesn't complete successfully, then the workstation cannot contact the server (i.e., the port is wrong or there's a firewall issues or something of that nature as suggested by joharder).  If the setup completes successfully using the correct ODBC setup, correct port, etc., then the problem is most likely a security issue, i.e., the user doesn't have a login for the SQL database or doesn't have appropriate rights to the database you're trying to use.
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Hypercat (Deb)Commented:
Also - another thought that just occurred to me is that you may not have the most up-to-date SQL client software on the workstation. If you're using 2008 or later, you may want to download and install the SQL Native Client 10, which is part of the SQL 2008 Feature Pack:
FrankBabzAuthor Commented:
Thank you all for trying to help me...   This  answers a couple of above questions.

The new Win7 PC is 64 bit hardware.  All other client PCs are also 64 Bit.

FYI.  "Frank" is an Admin on ALL Client PCs, the SERVER, and fully privileged in SQLEXPRES.

SQLEXPRESS on the server is Windows Authentication aka "Trusted".

I have 3 Client PC's:  One is an older Win7, and 2 Win Vista that all run my Server 2008 R2 accde AP just fine.

Therefore... I'm thinking this is not a server, port, or SQLEXPRESS issue.  And, the problem is in the new Win7 PC and my lack of knowledge to properly set up the OCDB.  

I ran SYSWOW64 odbccad32.exe  (the “exe”) from a shortcut I placed on my desktop.  I assume this is the correct 32 bit version Access 2007 requires.

I ran exe from an Admin account on the PC, who is only a standard user on the Server.  I think I also did it from the “Frank” account as well, as I was flailing about trying things.  I need to settle down and quit flailing (:-)

I will go back to office and make sure I am using the “Frank” account.  If that fails I will install the Native Client 10 suggestion.  If done…  will the “exe” now show SQL NC 10 as an option to choose?
FrankBabzAuthor Commented:
Also... as half the options in the "exe' are a mystery to me  I pretty much accept all the defaults...
Hypercat (Deb)Commented:
Once you install the native client version 10, you'll see a second SQL option when you create a new ODBC connection, which you'll want to select:  SQL Server Native Client 10.
FrankBabzAuthor Commented:
Ok.. I installed the SQL Server Native Client 10, built the "SYSTEM" OCDB as advised.  It connected.  Things are working better than they did before, but I have a strange issue or two.

Repeating myself:  Frank and Jim are administrators on the new PC,
Frank is an administrator on the Server as well, and SQLEXPRESS handles the Group of users.,
Jim is one of 5 users in that Group on the Server along with all the other users on the other old PCs and they are all working fine with accde AP from the Server on their PCs.

Frank now works Fine on new PC

Jim is not working Fine on new PC because the AP give SQLEXPRESS error in an "info/reply popup"  saying user (Jim) is a "Guest" not having permission.  It allow entering Frank's credentials in that "info/reply popup" but rejects Frank too.    That rejection of Frank seems impossible because I am an Admin everywhere possible including SQLEXPRESS....

Somehow Jim is not being qualified as being a member of the Group (where all other PC users in that Group are working Fine).   SQLEXPRESS thinks (or has been told) Jim is a Guest. Where is Jim losing his identity?  When new PC is talking to the Server?  or when Server is talking to SQLEXPRESS - ???  

I considered that Jim's account on the Server had an error, so I deleted his account and created another Jim account and put Jim back in the Group...  but that did not fix the problem.

Can you come up with ideas?    Is there a particular Account or Accounts that MUST be included on the Server share in the new PC I may have overlooked for Jim?   I have included SYSTEM, NETWORK, NETWORK SRVICE FRANK and JIM but not sure what is really needed on that server share.  

Thanks in advance for any thoughts or ideas....
FrankBabzAuthor Commented:
I checked AP to find that I early on attach some SQL tables DSN less.  I need to go to office tomorrow to see if User Name and Password are being used properly...  could be a bug in there....  if not, may explains problem...  will update this thread tomorrow.
Thanks....  Frank
Hypercat (Deb)Commented:
I don't think the problem is NTFS permissions, it sounds to me as though it's a problem with the SQL permissions.  Is the group that you're putting these users into given permissions to the database in SQL?  In other words, the group should be added to the Security in SQL with public permissions to the database that you want them to use.
FrankBabzAuthor Commented:
Good question....  recall there are other PC users in that same server Group and they are working fine.  The Group has permissions in SQL (not PC users).   This problem (I think) is either the PC itself perhaps (and unlikely) NTFS permission related, or my AP is screwing up with this new user...   I'll know more later... I have some ideas to test in the AP itself.
FrankBabzAuthor Commented:
On the new PC the my Adminstrator account is working fine with the Server accde.   Recall that what fixed this one was creating that System OCDB,

The other new PC administrator "Jim" fails.    Jim is in the same server Group with all other PC users that work fine.

On Jim, using msAccess 2007 I resorted to loading the ACCDB from the server and it loaded  and I found in the Navigator pane it looked like all my tables were there, but when clicked I was advised it needed SPN or credential and I tried, but that did not clear the issue (not sure I had SPN info correct).  So, I tried the "External tab"  "more" option to mess with its OCDB feature...  I found the System OCDB mentioned above, but it ended up prompting to provide SPN or credentials.  So, I even tried creating a new OCDB but it would not connect either.

For now I have run out of ideas.  It is all so very close to working, but no cigar.
Any thoughts or ideas most appreciated.
FrankBabzAuthor Commented:
I found older Vista PC and set up Jim.  Like on Win 7 PC when AP tries to link in a table the error is:
Connection Failed:
Sql State '2800'
SQL Server Error 18456
[Microsoft][ODCB SQL Server Driver][SQL Server][Login Failed for user 'CORP\Guest'
ODCB -- Connection to SQL Server TED\SQLExpress failed code 3151

Somewhere Jim was Admin on his PC, a member of a Group on TED, but ended up at SQLExpress thinking him as Guest.

Other users in the Server Group have no problem running the AP from their PCs, but not Jim.

BTW...  Jim works on a similar 2008 server at my home using the above Vista machine.   Thought when I took PC to office it could not fail....   but it fails at the office.

FYI I also disconnected existing Shares while in office, and reconnected anew at Office , but that didn't help.

I am out of ideas....   I really need help...  Thanks in advance...
FrankBabzAuthor Commented:
Note & FWIW:  I made sure all PC users had exactly the same permissions on their PC's server share.   Did not help Jim on his Vista machine whereas on that same machine user "Joe" works just fine.   Wouldn't that definately point to a server issue?
FrankBabzAuthor Commented:
I insured Native Client 10 ODCB installed and AP worked with PC primary User.
Swithed to Jim hoping above fixed problem... but AP failed.

On Jim PC I tried setting up (again) a new ODCB and was not able to connect.   Tried both Authentications Options (primary User, and Jim), and was always rejected as "user not authorized"   Even tried the ODCB created above by primary user....  did not work.

Does each user require their own ODCB?  Is one ODCB supposed to work for all users?
How does invoking msaccess.exe to run an AP know which ODCB to use?
Some apps will require User DSNs, and some will work off of system or file DSNs.  It sounds like your app may require a user DSN.

As I mentioned earlier, you can inject the user DSN into the user profile.  Create a user DSN for a user, take note of the reg location, and then export that entry as a .reg file.  Then, write a batch file where you insert that .reg file based on user logons, e.g., logon script.  I had tried to do the same thing via a GPP but got it to work easier/faster via a batch file.
Hypercat (Deb)Commented:
If you use a system DSN, as I had suggested, you have to use a SQL login account that has the appropriate permissions to the SQL database.  It appears that you're using Windows user authentication with your SQL database, so you would, as joharder suggested, need a user DSN instead.
FrankBabzAuthor Commented:
Thanks....  I apologize but do not know what a .reg file is...  purpose?  where does it get created  and gets placed where I tell it?  I will research to try to understand.

As for login credentials...  I have tried using my account (Admin everywhere) but the ODCB would not connect.

In next day or two I will try creating the DSN under my account and save it as a USER DSN....  
I did try to create the System DSN under the user account, but could not connect.  I will try the User option.

Thanks for trying to help me.
FrankBabzAuthor Commented:
With so much working this problem for Jim is driving me (and probably you) crazy.
I am using Native Client 10.  Yes on this same PC using my account the AP works (worked) fine even without the system DSN (as I recall).   So.... today...

 I deleted the old System DSN and tried the following to create a User DSN.  

On Page 1 it located the SQL server fine.  Pg 2 I tried using My (Admin everywhere) account credentials that works everywhere else, and I was told "Login Failed" for (me)...   It also failed to connect when I used Jim's credentials.

I also tried Windows Authentication and it replied that connection failed for "Guest" account.  Why it thinks Jim is a Guest makes no sense to me.

Thanks for trying to help,...    I welcome any thoughts you may have.

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
FrankBabzAuthor Commented:
I have no solution....  Points awarded to those who tried to help.
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 Server OS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.