Access to MSSQL connection across domains

[callump99]

Hi,

I have a problem I hope you are going to help me solve.

Sorry about the length of this post, but I thought it was worthwhile writing all the relevant things I could remember....

I have two domains that do not have a trust relationship between them and there is no prospect of being able to establish one. In one domain, the "client" domain, I have an MS Access DB that has been "upsized" (using the Wizard) to migrate the data to MSSQL. In the other domain (the server domain) I have the MSSQL server. All systems are Windows XP Pro, the MSSQL server is 2005 and the Access is 2003.

What I want to be able to do is connect to the MSSQL server from the client domain with the MSSQL server set to "Windows Authentication". I cannot use MSSQL authentication as this is not sufficiently "strong".

However, I have not been able to do this, despite the fact that I can map a network drive (that exists on the server) on the client system, using the SAME username and password that exists on the server domain.

The MSSQL has the user added as a user and has all the correct permissions, which has been tested by running the Access DB from a client within the server domain.

The Access upsize wizard migrates the Access tables to be ODBC linked tables, so I have been playing around with the connection string and have used ALL sensible combinations of the following options (including options not present):

DRIVER={sql server}; also tried {sql native driver}
DATABASE=<databasename>;
SERVER=<servername>;
Persist Security Info=True; also tried false
Trusted_Connection=no; also tried yes
UID=SERVER_DOMAIN\cpaterson;
UID=cpaterson;
PWD=apassword;

None of them work, where they don't throw up errors about invalid options the error that is logged at the MSSQL server is:

2009-11-13 11:46:05.43 Logon Error: 18452, Severity: 14, State: 1.
2009-11-13 11:46:05.43 Logon Login failed for user 'cpaterson'. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.110]

I have even gone as far as trying ADO connections rather than the ODBC connections (which I think might mean I would have to rework my Access code considerably), but I get the same error.

If I enable Mixed mode Authentication (and use an appropriate username and password) it works with ODBC and with ADO.

Am I trying to do the impossible?

Why can I map a drive but not connect to the MSSQL server?

Any suggestion how to solve my problem or where else I might get help?

Regards

Callum

 

[HiTechCoach]

Why can I map a drive but not connect to the MSSQL server?

Mapping a drive would only indicate the the machine with the SQL sever is can be reached from the client machine.

From trying to get remote access to a SQL server over the internet (which woulf probably be like being outside the domain), I have to be sure to enable the router and firewall to allow the post that the SQL server is listening[/b] for.

 

[callump99]

Sadly, the firewalls and routers etc are all OK, because if I enable mixed mode authentication I can login using an MSSQL username and password, but if I go back to windows authetication I cannot login. What is frustrating is that when logging-in to a share I can specify a remote (where the share is) username and password but I cannot seem to with the MSSQL login.

Callum

 

[HiTechCoach]

My understanding is that to use Windows Authentication, you must be using the same AD for login Authentication. It does not make sense to use your local Windows Authentication from your local domain to access a domain that you are not a member.

 

[giovaanni]

Use:

SERVER=<IPADDRESS>\<ServerName>,1433