Trusted SQL Server Connection - Tricky situation

[Aargee]

Hello All
I'm developing a small app in MS Excel (using VBA ofcourse) that needs to establish connection with SQL Server & run a code that is input. It turned out quite a challenge for me that I frankly never expected.

The Excel will reside on our local laptops that is in, say DOMAIN1 where we login as DOMAIN1\654321

The SQL Server instance & DB resides on DOMAIN2, where we login as DOMAIN2\LastFirstName. We have Administrative access to this instance ONLY using Windows Authentication.

From our local laptop, we use VPN connection to gain access to SQL Server systems. Which means, we're able to ping the server ip OR hostname ONLY when the VPN connection is being established.

THE PROBLEM - DSN or DSN Less connections are working only when the VBA code & SQL Server instance are on same network/domain

I've tried...

Attempt 1 - Which never worked!!
ConnectionString = "Provider=SQLOLEDB;Data Source=DBSERVER;Initial Catalog=DBNAME;Trusted_connection=Yes;Integrated Security=SSPI"
Set DBServer = New ADODB.Connection
DBServer.Open ConnectionString, "DOMAIN\LastFirstName", "Password"
DBServer.Close

Attempt 2 - Which never worked!!
ConnectionString = "Description=DSN For SQL Server" & vbCr & "SERVER=DBSERVER" & vbCr & "DATABASE=DNNAME" & vbCr & "Trusted_Connection=Yes"
DBEngine.RegisterDataBASE "SPDSNLess", "SQL Server", True, ConnectionString
DBEngine.OpenDatabase "DSN=SPDSNLess"

Attemp 3 - Again never worked!!
- Created a DSN (both ODBC & SQL Native)
- Tried changing the username & password dynamically using VBA

Attempt 4 - Testing if the connection actually works
- Installed SSMS
- Started SSMS from command prompt using "runas /noprofile /netonly /DOMAIN2\LastFirstName ssms.exe"
- It prompted for password & when supplied...VOILA!! It worked!! I was able to connect to DBSERVER\INSTANCE, which proved that it is actually possible to connect to SQL Server on other domain from local laptop

So my question - Please guide me as how the VBA code (either DSN Less or with DSN) has to be inorder to establish connectivity, but should be able to connect from our laptop logged in as DOMAIN1\654321 to SQL Server DBSERVER\INSTANCE which is on DOMAIN2\LastFirstName (accessible when we connect through VPN)

Thank you
RG

 

[Aargee]

156 views & no solution?? C'mon guys, please, there has to be some solution & someone should've done this before. Pls help!

 

[Rx_]

Do some searching on this site first. There are many code samples we have posted on this subject. I am suspect about the provider string.
Bottom line, you can use the published code.
My favorite is to download the free SQL Server Native Client 11.0 on a workstation. Then use DSN Less code to connect. This driver is prefered to connect Excel (or others) to the MS AZURE as well. If each desktop doesn't have good quality control for the ODBC, you might consider using the latest SQL Server Native Client 11.0 and just go around the whole ODBC dialogue.

If you post this question in the Excel section, we can also discuss how to create one public function on your network so all users in that network can reference it to use the connection procedure.
http://www.access-programmers.co.uk/forums/showthread.php?t=247756&highlight=odbc

This should get you started on the code solution for DSN-Less.

 

[Aargee]

Do some searching on this site first. There are many code samples we have posted on this subject. I am suspect about the provider string.

Thank you for your reply. I've Googled & Googled & googled and googled a lot & found this is one website which was greatly convincing that there're some good experts for DSN/DSN less/VBA/Excel & hence registered to find a solution.

So, my point is that, this forum has superb code examples, but none of them matches with the requirement that I'm looking at.

My tricky situation is that, I'm passing 2 domains, one for logging in & the other domain has SQL Server. I'm unable to change the login dynamically (especially with trusted connection it is not possible to provide the UID/PWD dynamically at run-time) to establish trusted connection, which eventually is CONNECTION STRING & hence my post.

So, please, don't feel that I haven't searched or did not do my homework & I simply jumped in to post a query. Pardon me, if I did give you that impression & please guide. Thanks.

 

[Aargee]

Update - I tried with SQL Server local account (sa) & it worked!! So its confirmed that the issue is NOT with connect string, but, when the domain changes, how to change the UID & PWD accordingly to get authenticated using Trusted_Connection.

Please guide. Thanks

Edit - The driver was "SQL Server Native Client 10.0"

 

[Rx_]

The native Client 10.0 is OK. I just recentlly upgraded to 11.0.
In the past I have connected to multiple domains.

Question: is your Excel links Read/Only?
I have done both, but 99% of the Excel links typically end up being Read Only.

If Excel is Read Only - my advice would be to set up a SQL Server View - with permissions of Read Only. Then, set them up with one single UN/PW
Example: User: ExcelReport PW:XLReports
Bonus: Have the two servers create views (different names) with the same Read Only UN/PW.
By setting up a single UN/PW, this makes a great test-bed for development and even for initial distribution. There is always the option to add each user's individual name later. But for now, just get the login out of the way.
The Read Only with a view of only the data needed (a View is a query with a security context) it protects the SQL Server.

Lets recap where you are at.
It sounds as if the sa permissions work. It can be a little tricky getting the individual users set up. This is from another of my post:
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=52714&d=1393260257
In code, create a text table for at least two tables with the servers:

Just re-read and think that you have this: (correct me if I didn't get this right)
1. One Login for one Server is with UN/PW
2. 2nd Login for another server is with Integretated (SQL Server Network ID)

In Access, we have the Linked Table Manager that allows multiple links to persist as an object.
Now I understand more about the challange.
Will wait for you to get back.

Another approach is to create a Linked Server in one SQL Server to the other SQL Server. This has huge advantages. You could then query the two data sources in SQL to create a View for Excel to consume. Excel would then have one login.
This might be a better approach from a maintenance point of view.

SQL Server Linked Server
http://www.access-programmers.co.uk/forums/showthread.php?t=270512&highlight=Linked+Server
I recentlly did a linked server from SQL Server to Oracle. It is a lot more complex than a SQL Server to SQL Server.
This is the concept of how it works. You can choose what one server can consume from the other. The result is that it looks like both sources get served from one single login.

 

[Aargee]

Hello Rx, thanks for your continued help. We're in a situation that we can't touch a thing on the DB servers, so linked servers & things can't be done.

Anyway, I've resolved the issue by using runas & was able to the other domain & got things done. Thanks again for your continued help.

 

[Rx_]

Explain more about the RunAs

 

[Aargee]

Explain more about the RunAs

Trying to post a link, but this forum is not allowing me to