Trusted connections to SQL Server via ADSI

FrankEinstein · **Joined:** Fri Dec 22, 2006 7:24 pm **Posts:** 13

How do I go about setting up a trusted connection to a remote SQL Server database authenticated via ADSI? There is no username and password, it's your ADSI authentication that determines whether you get in or not. I did this when evaluating Real BASIC about five years ago using SQL Server authentication, but that's no longer an option. It's Windows Authentication or bust, now. I tried passing my uid and pwd in the connection string as a test, but that doesn't work, either. Is this doable?

'This fails at the cnDatabase.Open statement.
Dim cnDatabase As New OLEObject("ADODB.Connection")
Dim rsTable As New OLEObject("ADODB.Recordset")
cnDatabase.Open "Provider=SQLOLEDB; Persist Security Info=True;Trusted_Connection=True;Data Source=ServerIPAddress;Initial Catalog=TestDB;"
rsTable.Open "SELECT LastName FROM People WHERE UID = 17113351", cnDatabase
While Not rsTable.EOF
MsgBox rsTable.Fields("LastName").Value
rsTable.MoveNext
Wend
rsTable.Close
cnDatabase.Close

FrankEinstein · **Joined:** Fri Dec 22, 2006 7:24 pm **Posts:** 13

Forgot to mention (and this could be quite germane to the discussion) that my development machine is not part of the domain. I have to launch Real Studio from a batch file that reads like this:
runas /netonly /user:MyDomain\MyUserName "C:\Program Files (x86)\Real Software\Real Studio 2012r2.1\Real Studio.exe"

I log into the domain when the batch file runs. This works perfectly from VB.Net. I make the connection using System.Data.SqlClient as slick as a whistle. I cannot seem to do the same with Real BASIC, though. Should I be using a different provider from SQLOLEDB?

jdorsey · **Joined:** Tue Jan 26, 2010 12:54 pm **Posts:** 29

Have you tried the MSSQLServerDatabase plugin?

From the LR:

Dim db as MSSQLServerDatabase
db=New MSSQLServerDatabase


db.Host = "192.168.0.1\InstanceName"  ' or just the IP if using the default instance
db.DatabaseName = "NorthWind"
db.UserName = "MyUserID"  ' or "Domain\UserID for trusted domain accounts
db.Password = "mypassword"

If db.Connect Then
  //proceed with database operations
else
  MsgBox "Connection failed!"
end if

FrankEinstein · **Joined:** Fri Dec 22, 2006 7:24 pm **Posts:** 13

Yeah, I tried the plugin, too. I still get the "Connection failed" MsgBox. I turned off my firewall, I tried explicitly setting the port number with db.port, I tried using the FQDN instead of the IP Address, I tried it without the username and password, I went back to the non-plugin version and messed around with that one for a while again, I tried everything I can think of to try and still no go. I can connect with SSMS and with VB.Net without even batting an eye, but I can't get anything out of Real BASIC.

I suspect this will not fly for me no matter what I try. I may have to wait another few more years for something to change on one of our ends and then try it again. Sigh.

jdorsey · **Joined:** Tue Jan 26, 2010 12:54 pm **Posts:** 29

Do you get any errors when looking at the SQL Server Logs on that machine?

FrankEinstein · **Joined:** Fri Dec 22, 2006 7:24 pm **Posts:** 13

I don't have access to the server logs, and getting a dba to look for me is an arduous task not to be undertaken lightly.

jdorsey · **Joined:** Tue Jan 26, 2010 12:54 pm **Posts:** 29

Assuming that the program is trying to make a connection to the Instance I think you should consider bothering your DBA for assistance. I can understand that the task might be a rough one but it also might give you some valuable information.

Could you check for OLEException? Or possible if you use the MSSQLServerDatabase you could query something like:

If db.connect = false then
  Msgbox db.errormessage + " " + Cstr(db.errorcode)
else
  
end if

I guess I'm wondering if the server will return an error about bad password or something or if it will just give a simple timeout.

FrankEinstein · **Joined:** Fri Dec 22, 2006 7:24 pm **Posts:** 13

The error I'm getting is that the SQL Native Client isn't installed. It absolutely is, though. I might be having the same issue as this guy:
http://forums.realsoftware.com/viewtopic.php?f=3&t=39085&hilit=sql+server+native+client+installed

I'm using the 64 bit client and trying to connect to SQL Server 2005.

FrankEinstein · **Joined:** Fri Dec 22, 2006 7:24 pm **Posts:** 13

I ran a get-wmiobject in PowerShell just to make doubly sure that I wasn't being stupid. I had installed the latest greatest version of the client early on in my debugging attempts, I could look at my backups for an earlier version, maybe.

Microsoft SQL Server 2012 Native Cli... sqlncli.msi 11.0.2100.60

s0uza · **Posted:** Mon Mar 25, 2013 5:38 am

Just an idea:
As Realbasic creates 32-Bit applications that naturally have to talk via 32-Bit drivers - did you check that you have installed the 32-Bit version of the native client?

FrankEinstein · **Joined:** Fri Dec 22, 2006 7:24 pm **Posts:** 13

Like the other guy with the same problem, the native SQL Client will not install 32 bit drivers on 64 bit Windows. It says they are incompatible and all that. I've been debating digging up a 32 bit machine to try this from, but I'm too busy with other stuff at the moment. I did get a look through my backups, but they are the older version of the 64 bit client and it makes no difference if I use the older or newer version, same result.

We implemented 64 bit enterprise-wide so we could run our mission-critical stuff, and finding an old 32 bit machine might not be possible. Even all my home machines are 64 bit, with the lone exception being a Lenovo ThinkPad Tablet2. I'll try this once I can dig up a 32 bit client for our Cisco VPN.

FCampos · **Joined:** Tue Nov 13, 2012 11:19 am **Posts:** 7

I have the same problem

Windows 64bits based O.S.
Sql Server 2005 Standard

I resolved using g ODBC connection

Dim db As New ODBCDatabase
db.DataSource = "driver={SQL Server};server=SERVER_NAME;databaseDATABASE_NAME;Trusted_Connection=Yes"

I hope this works for you.

Anyway, I supouse that the connection using the MSSQL Driver works faster than ODBC.

	Real Software Forums The forum for Real Studio and other Real Software products.
[ REAL Software Website \| Board Index ]

Real Software Forums

Trusted connections to SQL Server via ADSI