Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Express trusted connections issue

Posted on 2013-06-27
6
Medium Priority
?
433 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
[X]
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
  • 3
  • 3
6 Comments
 
LVL 27

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 27

Accepted Solution

by:
Zberteoc earned 2000 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 27

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This Micro Tutorial will give you a basic overview of Windows DVD Burner through its features and interface. This will be demonstrated using Windows 7 operating system.
This Micro Tutorial will give you a basic overview of Windows Live Photo Gallery and show you various editing filters and touches to photos you can apply. This will be demonstrated using Windows Live Photo Gallery on Windows 7 operating system.
Suggested Courses

610 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