Solved

SQL VB connection works in one PC and doesn't in another

Posted on 2016-11-03
15
42 Views
Last Modified: 2016-11-09
We have an application running within a windows server, with the following vb6 code to connect to it's sql 2008 instance:

 cnn.Open "Provider=SQLOLEDB; " & _
 "Initial Catalog=" & dbname & "; " & _
 "Data Source=" & sqlserver & "; " & _
 "integrated security=SSPI; persist security info=True;"

Open in new window

(Which makes the sql connection 'Provider=SQLOLEDB; Initial Catalog=DBNAME; Data Source=SQLINSTANCE; integrated security=SSPI; persist security info=True;')

The problem,
If the user is administrator, all works great, if the user is non-admin, doesn't connect and displays "Error number: -214721783".

We went into sql to the problem table and granted permission to select/delete/insert/update and still same error.  Note that the non-admin user has access to all other tables with the database, it's only with this table; thus, sql permission of some sort.

Please advice.
0
Comment
Question by:rayluvs
  • 9
  • 5
15 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41871950
Can this user connect to SQL Server using SSMS and run SELECT command ?
0
 

Author Comment

by:rayluvs
ID: 41871961
Just checked and logged into the server with the non-admin user name, ran SSMS, and no; cannot even log in.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41871966
Thats the issue. Please fix it !!

Give him the proper access :)
0
 

Author Comment

by:rayluvs
ID: 41871968
Please provide the steps.

Thank you.
0
 

Author Comment

by:rayluvs
ID: 41871976
FYY:

We went into Sql 2008 Instance >> security >> login and check the following:

- there is a password for the user in General,
- in server role he is 'public',
- in User Mapping he has access to the correct databases,
- in Securables is empty,
- in Status, permission granted and login enabled

What are we missing?
0
 
LVL 17

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
ID: 41871984
In User Mapping --> Database role member ship for

db_owner

, you will get this at the bottom of the screen.
0
 

Author Comment

by:rayluvs
ID: 41871996
Done.  Same error as stated in the question.  Also tried to get the user logged in and can't.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41871998
Pls post the error?
0
 

Author Comment

by:rayluvs
ID: 41872002
Error number: -214721783
Login failed for user SqlInstanceName\UserName
0
 

Author Comment

by:rayluvs
ID: 41872116
FYI,

Please note that user has access to all other tables within the database, it's only with a specific table.  The user as all users has been working with the database for more than 5 years without giving them owner rights to the database.

The problem is with this specific table that is used  thru a vb6 apps we have.  Again, all apps the users uses to access and work with the database, where the table was created recently, has no problem.

Since the problem is with this table only, what can de causing this problem?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41872241
Okay , So what they are storing in that table.  Sensitive info. ?
0
 

Author Comment

by:rayluvs
ID: 41872372
It's a new table storing customer specific info; not that sensitive data.
0
 

Author Comment

by:rayluvs
ID: 41873267
We have gone over and over this problem.   We have looked into what we think is everything and still searching; the only conclusion that we can come up with is window-user-permissions on table.

The curios thing,
  • When Windows administrator logs into Windows Server and logs in the application of said database, he has no problem with the new table created (a vb6 apps that uses it).
  • If the Admin logs into Windows Server and then logs to the application with a non-admin username (an SQL username that the apps uses), again, he has no problem with the our vb6 apps.
  • However, if the non-admin logs into Windows Server and logs in the application of said database, they have no problem, but as soon as they run the vb6 apps they get Error number: -214721783 'Login failed for user SqlInstanceName\UserName'
  • Finally, if the non-admin user logs into Windows Server and then logs in the application of said database with "SA" (SQL user), and run the vb6 apps they get the Error

So conclusion,
  • The problem has to be the permissions Windows User created in the Windows Server.
  • Why?
Because the same vb6 apps that accesses with the new table functions excellent when the user Administrator logs in the server and it same exact apps doesn't work when a non-administrator logs into the server.

Hope this info helps, help us.

UPDATE:
We created a new data base and a new table, pointed the apps there and same problem; a non-admin running the apps displays the error:

Error number: -214721783
Login failed for user SqlInstanceName\UserName

Hence, it's something in the SQL instance where a non-admin user cannot access new DB or Tables.
0
 

Author Closing Comment

by:rayluvs
ID: 41881485
Besides In User Mapping --> Database role member >> db_owner, we also granted permission.

Thanx!  Your entry gave us the lead!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Ransomware continues to be a growing problem for both personal and business users alike and Antivirus companies are still struggling to find a reliable way to protect you from this dangerous threat.
It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

707 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

16 Experts available now in Live!

Get 1:1 Help Now