Sage 100

I'm in the process up upgrading a client from 100 Advanced to Premium. They have a few custom buttons on various windows that are all linked to individual VBscripts. The connection string is referencing SOTAMAS90 as the DSN, sample below. I'm waiting for one of my colleagues to help out with the necessary changes but thought I would post here to get a start on this. What would be needed to connect to the SQL databases as the SOTAMAS90 driver doesn't exist in the Premium version. Would the SQL MAS_User account need to be part of the script or can the same sage login and password be used?

Set connectionToDatabase=CreateObject("ADODB.Connection")
connectionToDatabase.ConnectionTimeout=60
connectionToDatabase.Open "DSN=SOTAMAS90;" & _
"Company=COR;" & _
"UID=vi;" & _
"PWD=vi"

Thanks.

------------------------------
John Kerr
Net at Work
------------------------------

Create local workstation DSN's for each company code (MAS_###) using Windows authentication, and give users ready only access to the databases.
(If you do use one of the Sage logins, go with MAS_Reports, which is read only... MAS_User is read-write / dbo).
Converting to use BOI scripts instead of ODBC might be worth considering, since it is less maintenance.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Thanks Kevin. I will pass this on to my colleagues. I presume that the sign on credentials can be saved making it "silent connect" setting? The users complain about having to specify company code, user name and password, each time they use the current version buttons.

------------------------------
John Kerr
Net at Work
------------------------------

Original Message:
Sent: 03-20-2019 10:33
From: Kevin Moyes
Subject: Update VBscript with connection string to Sage SQL

Create local workstation DSN's for each company code (MAS_###) using Windows authentication, and give users ready only access to the databases.
(If you do use one of the Sage logins, go with MAS_Reports, which is read only... MAS_User is read-write / dbo).
Converting to use BOI scripts instead of ODBC might be worth considering, since it is less maintenance.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

That is where the named company specific DSN's using Windows authentication come in.  Within the DSN, set the default database for that DSN, and you don't have to choose anything runtime.
SQL Authentication passwords do not save within the DSN, which is why I don't recommend them.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 03-20-2019 10:43
From: John Kerr
Subject: Update VBscript with connection string to Sage SQL

Thanks Kevin. I will pass this on to my colleagues. I presume that the sign on credentials can be saved making it "silent connect" setting? The users complain about having to specify company code, user name and password, each time they use the current version buttons.

------------------------------
John Kerr
Net at Work
------------------------------


Original Message:
Sent: 03-20-2019 10:33
From: Kevin Moyes
Subject: Update VBscript with connection string to Sage SQL

Create local workstation DSN's for each company code (MAS_###) using Windows authentication, and give users ready only access to the databases.
(If you do use one of the Sage logins, go with MAS_Reports, which is read only... MAS_User is read-write / dbo).
Converting to use BOI scripts instead of ODBC might be worth considering, since it is less maintenance.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON

Should the DSN be created as a User or System DSN. I suppose if it's a system DSN then theoretically it could work for anyone using that machine. Would a system DSN automatically recognize windows authentication for any domain user logging onto the machine?

------------------------------
John Kerr
Net at Work
------------------------------

Original Message:
Sent: 03-20-2019 10:55
From: Kevin Moyes
Subject: Update VBscript with connection string to Sage SQL

That is where the named company specific DSN's using Windows authentication come in.  Within the DSN, set the default database for that DSN, and you don't have to choose anything runtime.
SQL Authentication passwords do not save within the DSN, which is why I don't recommend them.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------


Original Message:
Sent: 03-20-2019 10:43
From: John Kerr
Subject: Update VBscript with connection string to Sage SQL

Thanks Kevin. I will pass this on to my colleagues. I presume that the sign on credentials can be saved making it "silent connect" setting? The users complain about having to specify company code, user name and password, each time they use the current version buttons.

------------------------------
John Kerr
Net at Work


Original Message:
Sent: 03-20-2019 10:33
From: Kevin Moyes
Subject: Update VBscript with connection string to Sage SQL

Create local workstation DSN's for each company code (MAS_###) using Windows authentication, and give users ready only access to the databases.
(If you do use one of the Sage logins, go with MAS_Reports, which is read only... MAS_User is read-write / dbo).
Converting to use BOI scripts instead of ODBC might be worth considering, since it is less maintenance.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON

We use System DSN's, and yes Windows authentication is based on the Windows user running the process.
For the SQL permissions, a domain group makes that easier to manage.


------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 03-20-2019 11:03
From: John Kerr
Subject: Update VBscript with connection string to Sage SQL

Should the DSN be created as a User or System DSN. I suppose if it's a system DSN then theoretically it could work for anyone using that machine. Would a system DSN automatically recognize windows authentication for any domain user logging onto the machine?

------------------------------
John Kerr
Net at Work
------------------------------


Original Message:
Sent: 03-20-2019 10:55
From: Kevin Moyes
Subject: Update VBscript with connection string to Sage SQL

That is where the named company specific DSN's using Windows authentication come in.  Within the DSN, set the default database for that DSN, and you don't have to choose anything runtime.
SQL Authentication passwords do not save within the DSN, which is why I don't recommend them.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 03-20-2019 10:43
From: John Kerr
Subject: Update VBscript with connection string to Sage SQL

Thanks Kevin. I will pass this on to my colleagues. I presume that the sign on credentials can be saved making it "silent connect" setting? The users complain about having to specify company code, user name and password, each time they use the current version buttons.

------------------------------
John Kerr
Net at Work


Original Message:
Sent: 03-20-2019 10:33
From: Kevin Moyes
Subject: Update VBscript with connection string to Sage SQL

Create local workstation DSN's for each company code (MAS_###) using Windows authentication, and give users ready only access to the databases.
(If you do use one of the Sage logins, go with MAS_Reports, which is read only... MAS_User is read-write / dbo).
Converting to use BOI scripts instead of ODBC might be worth considering, since it is less maintenance.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON

I don't believe you need a DSN install on each workstation.  You can just build the connection string in VBScript to go direct to SQL with trusted Windows Authentication and bring in the MAS company in from the Sage company wildcard (which escapes me at the moment).  I've run out of time, but your connection would look something like this:

Dim con As New ADODB.Connection
con.ConnectionString = "Provider=SQLOLEDB;Server=SqlSERVER\SQLInstance;Database=MAS_ABC;Trusted_Connection=YES;" 
con.Open

------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯
------------------------------

Original Message:
Sent: 03-20-2019 11:03
From: John Kerr
Subject: Update VBscript with connection string to Sage SQL

Should the DSN be created as a User or System DSN. I suppose if it's a system DSN then theoretically it could work for anyone using that machine. Would a system DSN automatically recognize windows authentication for any domain user logging onto the machine?

------------------------------
John Kerr
Net at Work
------------------------------


Original Message:
Sent: 03-20-2019 10:55
From: Kevin Moyes
Subject: Update VBscript with connection string to Sage SQL

That is where the named company specific DSN's using Windows authentication come in.  Within the DSN, set the default database for that DSN, and you don't have to choose anything runtime.
SQL Authentication passwords do not save within the DSN, which is why I don't recommend them.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 03-20-2019 10:43
From: John Kerr
Subject: Update VBscript with connection string to Sage SQL

Thanks Kevin. I will pass this on to my colleagues. I presume that the sign on credentials can be saved making it "silent connect" setting? The users complain about having to specify company code, user name and password, each time they use the current version buttons.

------------------------------
John Kerr
Net at Work


Original Message:
Sent: 03-20-2019 10:33
From: Kevin Moyes
Subject: Update VBscript with connection string to Sage SQL

Create local workstation DSN's for each company code (MAS_###) using Windows authentication, and give users ready only access to the databases.
(If you do use one of the Sage logins, go with MAS_Reports, which is read only... MAS_User is read-write / dbo).
Converting to use BOI scripts instead of ODBC might be worth considering, since it is less maintenance.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON

Thanks Steve, very interesting. I was poking around with some settings yesterday and tried setting up the DSN to use the MAS_Reports user account, but the setup process doesn't allow me to save the password. Picking up on what @Kevin Moyes posted earlier, it would seem that I need to add a domain user group in SQL and assign permissions in order for windows authentication to work. I will need to check their AD and look for a Sage or Accounting group that can be added.
That string you included looks like a potential winner though.

I will keep you posted.​

------------------------------
John Kerr
Net at Work
------------------------------

Original Message:
Sent: 03-21-2019 08:49
From: Steve Iwanowski
Subject: Update VBscript with connection string to Sage SQL

I don't believe you need a DSN install on each workstation.  You can just build the connection string in VBScript to go direct to SQL with trusted Windows Authentication and bring in the MAS company in from the Sage company wildcard (which escapes me at the moment).  I've run out of time, but your connection would look something like this:

Dim con As New ADODB.Connection
con.ConnectionString = "Provider=SQLOLEDB;Server=SqlSERVER\SQLInstance;Database=MAS_ABC;Trusted_Connection=YES;"
con.Open

------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯
------------------------------


Original Message:
Sent: 03-20-2019 11:03
From: John Kerr
Subject: Update VBscript with connection string to Sage SQL

Should the DSN be created as a User or System DSN. I suppose if it's a system DSN then theoretically it could work for anyone using that machine. Would a system DSN automatically recognize windows authentication for any domain user logging onto the machine?

------------------------------
John Kerr
Net at Work


Original Message:
Sent: 03-20-2019 10:55
From: Kevin Moyes
Subject: Update VBscript with connection string to Sage SQL

That is where the named company specific DSN's using Windows authentication come in.  Within the DSN, set the default database for that DSN, and you don't have to choose anything runtime.
SQL Authentication passwords do not save within the DSN, which is why I don't recommend them.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 03-20-2019 10:43
From: John Kerr
Subject: Update VBscript with connection string to Sage SQL

Thanks Kevin. I will pass this on to my colleagues. I presume that the sign on credentials can be saved making it "silent connect" setting? The users complain about having to specify company code, user name and password, each time they use the current version buttons.

------------------------------
John Kerr
Net at Work


Original Message:
Sent: 03-20-2019 10:33
From: Kevin Moyes
Subject: Update VBscript with connection string to Sage SQL

Create local workstation DSN's for each company code (MAS_###) using Windows authentication, and give users ready only access to the databases.
(If you do use one of the Sage logins, go with MAS_Reports, which is read only... MAS_User is read-write / dbo).
Converting to use BOI scripts instead of ODBC might be worth considering, since it is less maintenance.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON

I like Steve's connection string better than the DSN.  (We rarely deploy this kind of script to users, and the DSN is easy to set up for a server based script / automation).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 03-21-2019 09:00
From: John Kerr
Subject: Update VBscript with connection string to Sage SQL

Thanks Steve, very interesting. I was poking around with some settings yesterday and tried setting up the DSN to use the MAS_Reports user account, but the setup process doesn't allow me to save the password. Picking up on what @Kevin Moyes posted earlier, it would seem that I need to add a domain user group in SQL and assign permissions in order for windows authentication to work. I will need to check their AD and look for a Sage or Accounting group that can be added.
That string you included looks like a potential winner though.

I will keep you posted.​

------------------------------
John Kerr
Net at Work
------------------------------


Original Message:
Sent: 03-21-2019 08:49
From: Steve Iwanowski
Subject: Update VBscript with connection string to Sage SQL

I don't believe you need a DSN install on each workstation.  You can just build the connection string in VBScript to go direct to SQL with trusted Windows Authentication and bring in the MAS company in from the Sage company wildcard (which escapes me at the moment).  I've run out of time, but your connection would look something like this:

Dim con As New ADODB.Connection
con.ConnectionString = "Provider=SQLOLEDB;Server=SqlSERVER\SQLInstance;Database=MAS_ABC;Trusted_Connection=YES;"
con.Open

------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯


Original Message:
Sent: 03-20-2019 11:03
From: John Kerr
Subject: Update VBscript with connection string to Sage SQL

Should the DSN be created as a User or System DSN. I suppose if it's a system DSN then theoretically it could work for anyone using that machine. Would a system DSN automatically recognize windows authentication for any domain user logging onto the machine?

------------------------------
John Kerr
Net at Work


Original Message:
Sent: 03-20-2019 10:55
From: Kevin Moyes
Subject: Update VBscript with connection string to Sage SQL

That is where the named company specific DSN's using Windows authentication come in.  Within the DSN, set the default database for that DSN, and you don't have to choose anything runtime.
SQL Authentication passwords do not save within the DSN, which is why I don't recommend them.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 03-20-2019 10:43
From: John Kerr
Subject: Update VBscript with connection string to Sage SQL

Thanks Kevin. I will pass this on to my colleagues. I presume that the sign on credentials can be saved making it "silent connect" setting? The users complain about having to specify company code, user name and password, each time they use the current version buttons.

------------------------------
John Kerr
Net at Work


Original Message:
Sent: 03-20-2019 10:33
From: Kevin Moyes
Subject: Update VBscript with connection string to Sage SQL

Create local workstation DSN's for each company code (MAS_###) using Windows authentication, and give users ready only access to the databases.
(If you do use one of the Sage logins, go with MAS_Reports, which is read only... MAS_User is read-write / dbo).
Converting to use BOI scripts instead of ODBC might be worth considering, since it is less maintenance.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON