0

I wrote an SQL query which has to run on the server. To call that query I am using a VBScript which is stored on the server. When I run that script from the server, the script runs fine. But, when I run the VBScript from my local machine, I get an error stating that access was denied for the SQL script it is calling. I can't seem to figure it out. The following is the VBScript I am using:

dim dbconn, connect, ObjFSO
connect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=DBName;Server=ServerName;Database=DBName;ID=USERID;Password=PASSWORD"
Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Set dbconn = CreateObject("ADODB.Connection")
dbconn.Open connect
dbconn.execute ObjFSO.OpenTextFile("\\ServerName\FolderPath\FileName.sql").ReadAll,128
Set dbconn = Nothing

The error I get is:

ss

Note: I do not get the error message seen in the image above, when I run the same VBScript from the server.

Is there a way to get this program to only run from the server where it is stored?

Am I just missing something really simple?

user692942
  • 16,398
  • 7
  • 76
  • 175
Joshua Bryant
  • 123
  • 2
  • 10
  • 1
    Since you're using integrated security, you'll need to make sure your AD user account on your local box has permissions to log in and to execute the sql script. – digital.aaron Feb 15 '17 at 17:27
  • @digital.aaron if that account is able to run the script from a remote desktop connection, would that imply that they have the permissions needed? When you say "AD user account" do you mean the user id and password specified in the vbscript? – Joshua Bryant Feb 15 '17 at 17:33
  • "AD user" in this context means "Active Directory user". This is generally regarded as your Windows log on username and password. In any case, @Kerry-Cakebread posted a good answer. – digital.aaron Feb 15 '17 at 18:45
  • @digital.aaron in this case they need to check they have `SELECT` permission on the database table. – user692942 Feb 15 '17 at 18:50
  • Related: [What is a Trusted Connection?](http://stackoverflow.com/questions/1250552/what-is-a-trusted-connection) – user692942 Feb 15 '17 at 19:02

2 Answers2

2

Perhaps you are logging into the server with one set of Windows credentials and logged into your desktop under a different username that doesn't have SQL access?

In any event, since the connection string you're using indicates SSPI security, it's going to try and use your Windows credentials to authenticate you, and completely ignore the username and password provided in the connection string.

Try something like this instead (see www.connectionstrings.com):

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;
User Id=myUsername;Password=myPassword;
user692942
  • 16,398
  • 7
  • 76
  • 175
Kerry Cakebread
  • 71
  • 1
  • 7
  • You were right, there was an issue with the login I was using, It wasn't that the login didn't have the correct credentials. It was that the login was conflicting with an active account and that it was using a windows login. So, to fix the issue all I had to do was make a dummy login which used actual credentials to login and give it the correct permissions. Now it works like a charm! – Joshua Bryant Feb 15 '17 at 18:27
  • Also, THANK YOU SO MUCH! – Joshua Bryant Feb 15 '17 at 18:27
0

The issue was with the user ID I was attempting to use. I made a new user ID which does not use a windows authentication to access the DB and boom, it works!

Joshua Bryant
  • 123
  • 2
  • 10
  • That's because [`Integrated Security=SSPI`](https://www.connectionstrings.com/microsoft-ole-db-provider-for-sql-server-sqloledb/trusted-connection/) means your current Windows Authenticated credentials are pass-through to the SQL Server and you are authenticated using Windows mode not SQL Server mode. Like Kerry said the user creds you passed were being ignored, only the current Windows Authenticated account is passed-through. – user692942 Feb 15 '17 at 18:57