Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved SQL Connection with Domain Account -- SQL User vs AD User

  1. How-to
0
Votes
Undo
  1. Michael Quintus
    Michael Quintus
  2. PowerBuilder
  3. Monday, 27 December 2021 15:11 PM UTC

Hi, Trying to connect to a SQL database with an AD account and not using a trusted connection. Is this possible with PB 2021 or do we need to continue using SQL users?

 

IOW 'adUser' is a domain account that has permissions to dbServer, but we are getting login failed when trying to connect.


 SQLCA.DBMS = "SNC SQL Native Client(OLE DB)"
 SQLCA.AutoCommit = True
 SQLCA.DBParm = "Database='XXX',DisableBind=1,TrimSpaces=1"
 SQLCA.Logid = 'adUser'
 SQLCA.LogPass = 'adPassword'
 SQLCA.ServerName = 'dbServer' 
 CONNECT USING SQLCA;

PB 2019R2 - Paste from Clipboard into Datawindow L...
Is a grid datawindow column visible or not?
Accepted Answer
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Friday, 11 February 2022 14:54 PM UTC
  2. PowerBuilder
  3. # Permalink
In searching online through SQL Server documentation and in the PB Connection Reference documentation for SNC and MSO providers, I do not find any options to implement what you are describing. SQL Server considers "Windows Authentication" to be the current Windows user that is logged in... i.e., a Trusted Connection". This appears to be how security in SQL Server.is designed. I think you'll have to use SQL Server Authentication for "service"-type connections.
Comment
There are no comments made yet.
Responses (3)
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Monday, 27 December 2021 16:55 PM UTC
  2. PowerBuilder
  3. # 1

Hi, Michael -

If by "AD account" you mean a Windows domain/user, then in SQL Server (you neglected to mention what version of SQL Server you are using), you define the Login as follows:

substituting the appropriate domain name and login name, of course. The above snapshot is taken from SQL Server Management Studio v18.10 running against SQL Server 2017.

Once the SQL Server login has been defined, configure your Transaction object properties or DB Profile in PowerBuilder as follows:

SQLCA.DBMS = "SNC"
SQLCA.ServerName = "xxxxxxx"
SQLCA.AutoCommit = False
SQLCA.DBParm     = "Database='xxxxx',Provider='SQLNCLI11',TrustedConnection=1"

The key is to include the TrustedConnection=1 parameter in the DBParm property (I've omitted other parameters we typically include in DBParm for brevity in this example).

Please note that you no longer specify the LogID or LogPass properties, as SQL Server uses Active Directory to obtain and verify the domain/login of the Windows user.

Also note: Using the newer, MSOLEDBSQL provider is the same, except the DBMS property is set to "MSO" instead of "SNC".

HTH, John

Comment
Michael Quintus
  1. Michael Quintus
  2. Monday, 27 December 2021 16:59 PM UTC
Trusted connection passes the logged on user, not a specified user; that's the issue we're having. Unless there is an easy way to impersonate the id we want to use, rather than the current logged in user.



Utilizing a generic application user and maintaining permissions that way instead of having to maintain permissions for each user of the application.



As for SQL environments, we have 12, 16, & 19 currently.
  1. Helpful
Michael Quintus
  1. Michael Quintus
  2. Monday, 27 December 2021 17:02 PM UTC
IOW, logged in user "smith@windows" is different than "systemUser", the latter of which we want to use to connect to the database. It works fine if it's SQL Server auth, doesn't work with Windows auth. I'm curious if this is even possible or if it's strictly SQL auth users.
  1. Helpful
Michael Quintus
  1. Michael Quintus
  2. Friday, 11 February 2022 14:54 PM UTC
Sorry John, I didn't receive notice you had replied again. Thanks for the help, that was kind of the answer I was resolved to. We switched from an AD account back to a SQL user and all is fine. Thanks again
  1. Helpful
There are no comments made yet.
Armeen Mazda @Appeon
Armeen Mazda @Appeon Accepted Answer Pending Moderation
  1. Monday, 27 December 2021 16:35 PM UTC
  2. PowerBuilder
  3. # 2

Your DB driver way too old.  I suggest trying the new MSOLEDBSQL driver.  https://docs.appeon.com/pb2019r3/connecting_to_your_database/ch03s04.html

Comment
Michael Quintus
  1. Michael Quintus
  2. Monday, 27 December 2021 16:56 PM UTC
Are domain users supported with MSOLEDBSQL? I tried both old and new with same results.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.