Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-16-2017, 03:55 AM   #1
SteveVS
Newly Registered User
 
Join Date: Feb 2017
Location: south Africa
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
SteveVS is on a distinguished road
DSN-less connection not storing password

I am aware of the security risk his type of solution exposes but due to reasons im forced to use this approach
I need to store the sql login credentials in the connection string
I have succesfully done this for a other application but it doesnt seem to work again

When the code runs it shows cleary that the connection string is set to use the sql credentials and not windows auth but the table shows that it using windows auth

Code:
'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
    
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
      
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
    
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function
called using

Code:
call AttachDSNLessTable("BatchQIS_QTY","PartConfig.BatchQIS_QTY","SERVER","DB","USR","PWD")

SteveVS is offline   Reply With Quote
Old 08-17-2017, 03:26 AM   #2
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,981
Thanks: 75
Thanked 412 Times in 372 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: DSN-less connection not storing password

That is the exact code I use, so the code itself is good.

My first suggestion would be to step through the procedure using a break point and F8, and see what value is actually getting assigned to stUsername.
__________________
This is a test.
This is only a test.
If this were a real emergency, you would NOT have been informed, and we would have fled screaming in terror.
Frothingslosh is offline   Reply With Quote
Old 08-17-2017, 03:57 AM   #3
SteveVS
Newly Registered User
 
Join Date: Feb 2017
Location: south Africa
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
SteveVS is on a distinguished road
Re: DSN-less connection not storing password

That is what is the odd part.
As I run through the code stUsername is set "USR"
I can see this clearly within the variable and in the connection string

SteveVS is offline   Reply With Quote
Old 08-17-2017, 05:45 AM   #4
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,981
Thanks: 75
Thanked 412 Times in 372 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: DSN-less connection not storing password

When you stepped through it, which branch of the IF...THEN loop did it follow?

And what does the connection string for the table show afterward when you hover your mouse over it?

A screenshot would be amazingly helpful if you can put one up - just black out or clip any confidential data.

EDIT: Ignore this - see the next post.
__________________
This is a test.
This is only a test.
If this were a real emergency, you would NOT have been informed, and we would have fled screaming in terror.

Last edited by Frothingslosh; 08-17-2017 at 05:54 AM.
Frothingslosh is offline   Reply With Quote
Old 08-17-2017, 05:52 AM   #5
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,981
Thanks: 75
Thanked 412 Times in 372 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: DSN-less connection not storing password

Hold it, hold it, hold it.

You said WINDOWS Authentication, not Server Authentication?

That's a setting that has to be changed in SQL Server - it was set when the server was first installed.

https://docs.microsoft.com/en-us/sql...ntication-mode
__________________
This is a test.
This is only a test.
If this were a real emergency, you would NOT have been informed, and we would have fled screaming in terror.
Frothingslosh is offline   Reply With Quote
Old 08-17-2017, 11:43 PM   #6
SteveVS
Newly Registered User
 
Join Date: Feb 2017
Location: south Africa
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
SteveVS is on a distinguished road
Re: DSN-less connection not storing password

The settings are set to use it that way and has been from the beginning
But the problem still persist

The steps run through the part where it sets the credentials to use sql server but it stills default to windows auth

What confuses me is that this solution worked on another Access db using the same code and the same sql credentials and the same sql db
SteveVS is offline   Reply With Quote
Old 08-18-2017, 01:33 AM   #7
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,783
Thanks: 139
Thanked 1,561 Times in 1,533 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: DSN-less connection not storing password

You haven't got a local SQL instance running by any chance? Like on a dev machine?


__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Reply

Tags
dsn;sql server;access2010 , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Suppressing User ID/Password prompt on connection MrFuels Modules & VBA 0 06-16-2011 09:55 AM
Include Password in VBA for ODBC Connection Nevsky78 Modules & VBA 5 12-23-2010 12:44 AM
Connection to password protected db trueness Modules & VBA 2 04-30-2010 08:50 AM
ODBC File DSN Connection No Password Required ? mous General 2 05-23-2007 06:29 AM
odbc connection password issues? mlh407 General 1 08-21-2005 11:57 AM




All times are GMT -8. The time now is 10:34 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World