Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Express trusted connections issue

Posted on 2013-06-27
6
404 Views
Last Modified: 2013-07-01
We have a problem with an old application using local accounts and trusted connections to SQL.

Old setup that works fine:
•Computer SERV has SQL express installed on it
•VB app from another computer needs to connect to SERV to access SQL - in total two VB apps access the SQL in SERV: the one on COMPUTER1 and the one on SERV
•both computers are installed in a truck, part of our mobile fleet, so no local domain controller and intermittent access to Internet
•This is how it works presently: VB application running on COMPUTER1(Windows XP) under a local account(COMPUTER1\dispense)connects to SQL Express 2005 instance running on another computer SERV(Windows XP). In SQL server the access is granted to user SERV\dispense and to make it work it looks like in the key manager on COMPUTER1 the credentials for SERV\dispense are stored so it would appear COMPUTER1 is impersonating the local account defined on SERV.

We need to move this app onto Windows 7 and I need advice on the best way to do this. If we simply setup the app in the same way and cache the credentials of the foreign account like on Windows Xp(above) we get an error: Login failed for user ". The user is not associated with a trusted SQL Server connection.

It seems this, impersonation, works differently under Windows 7 - how can we fix this and/or how should we be doing this?

Thanks in advance
0
Comment
Question by:ee-gd
  • 3
  • 3
6 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39282090
How does the connection string look in the vb apps?

Have you tried to give  COMPUTER1\dispense user access to the database? Can the app from SERV box access the SQL instance?
0
 

Author Comment

by:ee-gd
ID: 39282125
the database is on computer SERV so COMPUTER1\dispense is not a local account on SERV and cannot be added in SQL - if you try to add it: it says Windows NT user or group not found because it cannot find the account COMPUTER1\dispense locally on SERV

as for the connection string itself - i don't have that, but will post when i obtain it.
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39282190
You can create a SQL login on the SQL server and give it a password, and then use that user/password in the app connection string. In this case it will not be a trusted connection anymore but you will be able to access the SQL server if it is accessible on the local network for from COMPUTER!. You can use it even locally.

Trusted connection is there mostly for the network domain context so if you don't have domain then is easier to use users with SQL authentication.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:ee-gd
ID: 39282471
I suggested using SQL logins before, but was told that it is hard and time consuming to implement because all their DLLs use trusted auth etc. Admittedly not a simple app and with many plug-ins, but it is not inconceivable that a connection string can't be changed - unless the trusted stuff is hard coded in too many places and not documented properly etc.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39282772
The way things look in some way that info will have to be changed unless really there is a setting in windows 7 to replicate the situation in the old OS. It will be a problem if for security reasons that would not be possible anymore in W7.
0
 

Author Comment

by:ee-gd
ID: 39289556
it turned out we were trying to connect from Windows 7 to Windows Xp machine in the lab and will be switching to sql logins as you suggested.

For reference a chap from Microsoft in a TechNet forum confirmed that as long as passwords are identical we should be able to connect usign trusted authentication and we can, but only when both machines are either running WinXp or Win7. Mixing the two did not work, but it works fine when both machines are running Windows 7.


If COMPUTER1 has an account COMPUTER1\dispense with password XYZ123ab and if SERV has an account SERV\dispense  with password XYZ123ab and if the SQL Server on SERV has a Windows Authentication login for SERV\dispense then you should be able to connect using Windows Authentication. The account names and passwords on both computers must match. This hasn't changed since Windows XP. In fact I just checked it connecting from Windows Server 2012 to Windows 8.1. Still works.
--------------------------------------------------------------------------------

Rick Byham, Microsoft
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you try to share a printer , you may receive one of the following error messages. Error message when you use the Add Printer Wizard to share a printer: Windows could not share your printer. Operation could not be completed (Error 0x000006…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This Micro Tutorial will teach you the basics of configuring your computer to improve its speed. It will also teach you how to disable programs that are running in the background simultaneously. This will be demonstrated using Windows 7 operating…
Viewers will learn how the fundamental information of how to create a table.

839 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