Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Unanswered: VPN Access to SQL

    First let me say that I'm new to SQL and have managed to set up my first server by luck and not a huge amount of understanding.

    I have 6 users all connecting to the server, using their windows logins, with no problems. One even connects through a VPN, in another office and that works fine too. However, one of them is now connecting through VPN at home, using their personal PC and they are getting an error 18452

    [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted AQL Server connection.

    When the SQL Server Login Dialog pops up if they enter their windows username and password it still doesn't work. The only way I can bodge it is to change their login from 'Windows User' to 'Standard', but they still get prompted to enter their details. Is there anything I can do to allow the home user to login without having to enter their username and password every time?

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62
    how are they logging in to the DB? With EM?

  3. #3
    Join Date
    Apr 2004
    Posts
    4
    The users in the office don't have to perform any further login than to their PC in the morning. The dB is setup to authenticate them with their Windows logins. This is why I think the home user is having problems as they are logging onto their home PC with a username of 'Admin' or some Windows default, which is then throwing the SQL server.

  4. #4
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    U won't be able to connect to SQL unless u use Impersonation for ur windows users. Otherwise use SQL authentication. Since u r accessing from home and there will be no trust-relationship; SQL denies bcz the user is not a trusted user. However u must be able to connect using SQL-Authentication and make sure TCP/IP protocol is enabled at ur SQL-Server.

    Howdy!

  5. #5
    Join Date
    Apr 2004
    Posts
    4
    TALAT - So what you're saying is that what I have at the moment is the only way I can do it.

    Is there a way of getting the user to enter their SQL login in a neater fashion than letting a SQL error come up and then them getting prompted?

  6. #6
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    Hi there. I don't think u would get an error if u r a valid user to SQL. How come u expect SQL to authenticate if the user u r tryin to get into SQL is not a valid windows user? Either user SQL-Authentication(no error would popup if u enter the correct user and password) or use impersonation to authenticate the user on the operating-system b4 u access SQL. While accessing thru SQL-Authentication, SQL needs to be running in mixed-mode.



    Howdy!

  7. #7
    Join Date
    Apr 2004
    Posts
    4
    Currently the server is running in mixed mode. What do you mean by impersonate? The user logs onto the server through VPN, using their own username and password (their windows username and password). When they try to browse the server for files they once more get asked to enter their username and password. Then that's it. The user having problems opens the dBase, but when the first transactions goes through, they get the SQL error about a problem login. I've tried entering their account into SQL Server as both 'Windows User' doesn't work and as 'Standard', works. However, both still bring up an error and prompt for a username and password. If these are entered when the user is a 'Windows User' the connection stills fails, however when the user is a 'Standard' it works.

  8. #8
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    I couldn't understand why u r asked to once more to enter the username and passw. First u establish a VPN connection to ur SQL-Server right. Now that PC is accessible. What do u do next? Log into the server using Terminal-services or just open the EM from ur system and connect to SQL. If u log into the SQL machine using terminal services and have appropriate local rights on that machine u must be able to connect. It's just like u r a local user to that machine. However, if u just establish a vpn connection to sql machine and then try to connect to the DB using a client machine, u must either use SQL-Authentication or setup impersonation. Impersonation allows a user to authenticate on the target server if the loca user has same password and the target server allows this. Check "the account is trusted for delegation" option at the domain-controller. U can find further help regarding impersonation in the operating-sys.

    Howdy!

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You are correct, in that when you try to open a trusted connection it passes your windows credentials to the SQL Server. It doesn't matter if you are using VPN, in the office, or on the server itself.

    You can make the home machines members of your office domain, but that's ugly. You can establish trust relationships with the home machines, but that's an administrative nightmare. You can use SQL authentication, which makes the process pretty smooth from an administrative perspective.

    One thing you can do that may simplify things quite a bit is to create a System DSN on the home machines. This allows you to enter and save the SQL credentials on the machine. Those credentials can only be used when VPN is active, so your security hasn't really changed, but the process is simpler for your users.

    -PatP

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    THey way I do it will require for you to modify your FE a little. If I want to use DSN-less connection (and I always want to use it), I store values that Pat refers to in the local registry instead of creating a System DSN. When the application gets executed for the first time the login box gets displayed. If a successful login occurred, those values are written to the registry and are retrieved on each successive execution without the login box being displayed. If the password got changed on the back-end, the login pops up because of the failed connection attempt. Once the correct pwd has been entered, the box disappears until the next time something like this happens. I wrote a little utility for managers using this concept, but in all serious besiness-related apps I prefer a login to be displayed every time the user invokes the app. UID, Server, DB Name, and Network Library selections are remembered from the previous execution, but the password always has to be entered.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    Hi, sir, how do we use DSN-Less connection; Entries for System-DSN are also stored in the Local-Registry. Can u plz explain how to store such values in the registry without creating DSN. Seems to be a good technique. I've a client-server VB application that connects to a production DB through a file dsn placed on a share. I can't put the connection info on each client(too many clients). But since the connection file is on share, the users can view the file entries like server which they are connecting to and other info. It would be fairly helpful if there is a way that i could encrypt or put the connection entries into registry by hiding the password, etc on a common PC. I can't embedd the connection info into the application because i use to change connection info oftenly.
    Sorry that i've slightly changed the topic of the thread.


    Howdy!

  12. #12
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  13. #13
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    Hi, Satya, thanx. In these examples; the connection string is hardcoded into the appliation. Means if i need to change the connection to another SQL machine, i would have the appliation recompiled and installed on the clients with new connection string.

  14. #14
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •