Domain Trust

Question

Domain Trust

jscriptor

SSC Enthusiast

Points: 101
More actions
July 6, 2012 at 2:17 pm

#263484

Hi
I have a sql server 2005 installed. I also have a ACCESS 2010 DB that I use for visulization of data. I have a connection set up using
con.Open "Provider=SQLOLEDB.1;Data Source=server,1433;Initial Catalog=aDB", "aUID", "aPW"
The ACCESS DB works fine when I am in the same Domain. However, when I user on a different domain tries to use the Access DB, the connection fails.
I am assuming it is a domain trust issue.
the SQL DB has "Allow remote connections to this serer" checked. the port is 1433, TCP/IP and named pipe is enabled, and I have SQL Server and Windows Authentication mode selected.
Is there a way to set up SQL Server 2005 to accept a connection from a user in a domain different from the domain where the SQL box is? same company ... different countries/geographical locations.
Thanks
Mike

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 1

Instead of connecting using the server name, which won't be resolving on other domains, you need to use the server IP address and port. Also, you need to be sure that IP address and port are accessible through the domain firewall. Be careful with this set up. I'd recommend changing your port from the default, 1433, because that's used by most malicious attacks. Make sure you have very good passwords on your logins and that they have minimal privileges. Finally, I'd suggest completely disabling the 'sa' account and using a different login as system administrator. 'sa' is a known attack point too.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

jscriptor SSC Enthusiast Points: 101 More actions · Answer 2

Hi

Thanks for the reply.

I am using the following procedure to get the server ip .... This was in a post on the web.

ALTER Procedure sp_get_ip_address (@ip varchar(40) out)

as

begin

Declare @ipLine varchar(200)

Declare @pos int

set nocount on

set @ip = NULL

Create table #temp (ipLine varchar(200))

Insert #temp exec master..xp_cmdshell 'ipconfig'

select @ipLine = ipLine

from #temp

where upper (ipLine) like '%IP ADDRESS%'

if (isnull (@ipLine,'***') != '***')

begin

set @pos = CharIndex (':',@ipLine,1);

set @ip = rtrim(ltrim(substring (@ipLine ,

@pos + 1 ,

len (@ipLine) - @pos)))

end

drop table #temp

set nocount off

end

go

declare @ip varchar(40)

exec sp_get_ip_address @ip out

print @ip

it returns the ip address fro the machine ... same address as what I would get if I type ipconfig in a DOS window.

using the machine IP for the server name I get this error

"[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

this is what I am starting with

con.Open "Provider=SQLOLEDB.1;Data Source=x.yy.zzz.www,1433;Initial Catalog=aDB", "aUID", "aPW"

In this try, I an calling from within the domain and have not change the port or the sa account yet.

Any thought about this would be appreciated.

Mike

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 3

Validate that the server is configured to make connections through IP. You can do this in the Configuration Manager (a separate app installed on the server or your desktop). But don't forget you have to sweat the firewall too. You should be able to simply substitute the server name with the IP address. That's all that happens in the background anyway.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

jscriptor SSC Enthusiast Points: 101 More actions · Answer 4

Hi Grant

Thanks for the help. I got it to work per your feedback. Many thanks

Mike

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 5

Thanks for the feedback. It's always good to know what worked.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning