Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 100
  • Last Modified:

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

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
rayluvs
Asked:
rayluvs
  • 9
  • 5
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Can this user connect to SQL Server using SSMS and run SELECT command ?
0
 
rayluvsAuthor Commented:
Just checked and logged into the server with the non-admin user name, ran SSMS, and no; cannot even log in.
0
 
Pawan KumarDatabase ExpertCommented:
Thats the issue. Please fix it !!

Give him the proper access :)
0
New Tabletop Appliances Blow Competitors Away!

WatchGuard’s new T15, T35 and T55 tabletop UTMs provide the highest-performing security inspection in their class, allowing users at small offices, home offices and distributed enterprises to experience blazing-fast Internet speeds without sacrificing enterprise-grade security.

 
rayluvsAuthor Commented:
Please provide the steps.

Thank you.
0
 
rayluvsAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
In User Mapping --> Database role member ship for

db_owner

, you will get this at the bottom of the screen.
0
 
rayluvsAuthor Commented:
Done.  Same error as stated in the question.  Also tried to get the user logged in and can't.
0
 
Pawan KumarDatabase ExpertCommented:
Pls post the error?
0
 
rayluvsAuthor Commented:
Error number: -214721783
Login failed for user SqlInstanceName\UserName
0
 
rayluvsAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Okay , So what they are storing in that table.  Sensitive info. ?
0
 
rayluvsAuthor Commented:
It's a new table storing customer specific info; not that sensitive data.
0
 
rayluvsAuthor Commented:
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
 
rayluvsAuthor Commented:
Besides In User Mapping --> Database role member >> db_owner, we also granted permission.

Thanx!  Your entry gave us the lead!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now