Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to convert an Access database to use a SQLServer backend. I've upsized the database and everything works on the server, but I'm unable to access it remotely.

I'm running SQLServer Express 2005 on Windows Server 2003. The server is not configured as a domain controller, nor connected to a domain. The computers I'm trying to access the server from are part of a domain, but there are no local domain controllers. I'm at a remote location and the computers are configured and connected to the domain at the home office, then shipped to us. We normally log in with cached credentials and VPN into the home office when we need to access the domain.

I can use Remote Desktop Connection to access the 2k3 server which is running SQLServer. If I log into the server with my username, I can bring up the database, access it via the Trusted Connection, and the database works. If I try to run the database locally, however, I get the Server Login dialog box. I can not use a Trusted Connection because my local login is to the home office domain and is not recognized by the SQLServer machine. If I try to use the username/password that is local to the SQLServer, I get a login failed error. I've tried entering the username as "username", "workgroup/username" (where "workgroup" is the name of the workgroup on the SQLServer), "sqlservername/username" and "username@1.2.3.4" where "1.2.3.4" is the IP of the SQLServer. In all cases, I get a login failed error. As I said, I can login to the server via Remote Desktop Connection with the same username and password and use the database, so permissions for the username appear to be correct for both a remote connection and for database access. Not sure where to go from here and any assistance would be appreciated.

share|improve this question
    
The issues here don't have much of anything to do with Access or SQL Server -- they are all networking/domain/authentication/VPN issues. You might have more luck with this kind of question on ServerFault.com. –  David W. Fenton Mar 15 '10 at 22:00
add comment

2 Answers

Background

Microsoft SQL Server supports two different authentication methods: SQL Server authentication and Windows authentication. It's important to understand what the difference is to set up a SQL Server.

SQL Server authentication requires the SQL Server itself to maintain a database of usernames and passwords that are allowed access to the database. The SQL Server process is responsible for authenticating users by comparing the username and password (hash) against its own database. This method of logon does not allow single sign-on because it's not integrated with Windows, and the credentials you provide are completely unrelated to Windows account credentials (domain or local).

Windows authentication uses standard Windows authentication to access the database. SQL Server is still responsible for authorization ("Is Bob allowed in?"), but Windows now becomes responsible for authentication ("Is that really Bob?"). You cannot enter credentials when using Windows authentication; this is why the username and password input boxes are disabled in SQL Server Management Studio when you select Windows Authentication. Whichever user the client program is running as is the user that is authenticated to the SQL Server (you can override which user gets authenticated by using runas /netonly).

When a program attempts to access the SQL Server via Windows authentication, SQL Server asks Windows to authenticate the user. If it's a local user, Windows checks its local user database and returns yea or nay. If the account is a domain account and the computer is joined to any domain, Windows hands off to Active Directory for authentication. Active Directory will validate the user if it exists somewhere in the trusted forest (either the current domain, or in a domain which is trusted).

Your situation

Your copy of SQL Server is running on a non-domain computer. When you Remote Desktop into the computer, you are running programs as a local user. When you "Use Trusted Connection" (which really means "Use Windows authentication"), Windows knows who your local account is and validates it. SQL Server then allows you access.

When you run a program on a domain-joined computer and try to authenticate via Windows Authentication, you are trying to authenticate to a non-domain computer using a domain credential. The non-domain computer doesn't know how to authenticate via domain (by definition, it doesn't trust the domain), so the authentication fails.

Possible solutions

  1. Use SQL Server authentication. Use a tool such as SQL Server Management Studio to create SQL Server logins for each user that needs access. You will not be able to use the "Use Trusted Connection" checkbox, and users will always need to enter their credentials for the SQL Server. Depending on what program is trying to access the SQL Server, it may provide some method for saving credentials in the registry or the users secure local storage so the user doesn't have to type them every time.

  2. When you run SQL Server Management Studio locally, run it with the runas command as such:

    runas /user:username /netonly "C:\Path\to\SSMS.exe"
    

    This will allow you to use Windows Authentication because instead of passing your logged-in credentials, you'll pass credentials for the "username" account. This account should be one that exists on the target machine.

  3. Join the computer running SQL Server to the domain and use. (You might also need to run the SQL Server as a domain account rather than a local account, but I'm not sure.) At that point, the SQL Server would be able to authenticate users using Active Directory, and "Use Trusted Connection" will work without entering any credentials. Of course, you'll still need to decide which users are authorized to access the database; you can use SQL Server Management Studio for this.

  4. Promote the server running SQL Server to a domain controller of a brand new domain (of which it is the only member). Then you can create a trust between the two domains so the SQL Server computer can send credentials to the other domain for authentication, and Windows Authentication will work. No credentials need to be entered. You'll still need to authorize users in SQL Server Management Studio.

share|improve this answer
    
While your answer has lots of nice information in it, it seems the main issue is the VPN not being properly configured. If the VPN were up and running properly, connecting to the SQL Server remotely would be exactly the same process as connecting to it in the Remote Desktop session (except for the speed issue). So, I can only conclude that the VPN is not properly configured. –  David W. Fenton May 24 '10 at 16:20
    
@David, While it's possible the VPN is also configured improperly, btrey's description of the situation makes it a certainty that a workgroup computer (the SQL server) is not going to be able to authenticate a domain user account. If you think the VPN may be configured incorrectly, please add a new answer describing what you suspect the problem is and how to solve it. –  Stephen Jennings May 25 '10 at 5:41
add comment

Have you both enabled TCP/IP connections and unblocked all the relevant ports (I think 1433, but I think it changes/automatically increases) on your firewall?

Lastly, if you want workgroup access, make sure that SQL server is set to mixed mode access and not just integrated security. Also, you may need to manually create logins for each user / group within SQL server.

share|improve this answer
    
Well, I've made progress. I tracked down the logs and the error codes for the failed logins, which turned out to be "invalid user." I had added the windows login to the database and given it permissions, which allowed a locally logged in user to access the database. But logging in remotely, I was apparently unable to authenticate through the Windows login. I added a new login to SQL Server and am now able to access the database remotely - sort of. See next comment. –  user24803 Jan 14 '10 at 17:29
    
When I access the database, I get a "Can not generate SSPI context" error and up pops the Login dialog. I have to manually uncheck the "Use Trusted Connection" dialog, then enter the username and password for the SQL Server login. The query or whatever I'm doing then goes through. Unfortunately, the next time I do something like run a query, I have to go through the same process again. Is there no way to cache the credentials or something? Having to reenter your username/password every time you do something is not going to work. –  user24803 Jan 14 '10 at 17:33
    
I think I assumed that when connecting via RDP it was a different PC running the SQL Server than was running the RDP session. So, you are, in fact, correct. –  David W. Fenton May 25 '10 at 17:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.