Solved

Help getting ODCB working on Wiin7 client to SQLEXPRESS on server

Posted on 2014-09-22
20
142 Views
Last Modified: 2014-10-10
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....
0
Comment
Question by:FrankBabz
  • 12
  • 5
  • 2
  • +1
20 Comments
 
LVL 24

Expert Comment

by:lionelmm
Comment Utility
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?
0
 
LVL 14

Expert Comment

by:joharder
Comment Utility
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.
0
 
LVL 38

Expert Comment

by:Hypercat (Deb)
Comment Utility
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.
0
 
LVL 38

Expert Comment

by:Hypercat (Deb)
Comment Utility
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:

http://www.microsoft.com/en-us/download/details.aspx?id=16978
0
 

Author Comment

by:FrankBabz
Comment Utility
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?
0
 

Author Comment

by:FrankBabz
Comment Utility
Also... as half the options in the "exe' are a mystery to me  I pretty much accept all the defaults...
0
 
LVL 38

Expert Comment

by:Hypercat (Deb)
Comment Utility
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.
0
 

Author Comment

by:FrankBabz
Comment Utility
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....
0
 

Author Comment

by:FrankBabz
Comment Utility
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
0
 
LVL 38

Expert Comment

by:Hypercat (Deb)
Comment Utility
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.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:FrankBabz
Comment Utility
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.
0
 

Author Comment

by:FrankBabz
Comment Utility
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.
0
 

Author Comment

by:FrankBabz
Comment Utility
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...
0
 

Author Comment

by:FrankBabz
Comment Utility
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?
0
 

Author Comment

by:FrankBabz
Comment Utility
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?
0
 
LVL 14

Assisted Solution

by:joharder
joharder earned 225 total points
Comment Utility
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.
0
 
LVL 38

Assisted Solution

by:Hypercat (Deb)
Hypercat (Deb) earned 275 total points
Comment Utility
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.
0
 

Author Comment

by:FrankBabz
Comment Utility
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.
0
 

Accepted Solution

by:
FrankBabz earned 0 total points
Comment Utility
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.
0
 

Author Closing Comment

by:FrankBabz
Comment Utility
I have no solution....  Points awarded to those who tried to help.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Scenario:  You do full backups to a internal hard drive in either product (SBS or Server 2008).  All goes well for a very long time.  One day, backups begin to fail with a message that the disk is full.  Your disk contains many, many more backups th…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now