What's wrong with this connection string?

[VBExplorer12]

Hi,

I'm using a service account, non-windows authentication.

Set Connection_o = CreateObject("ADODB.Connection")

' ConnectionString_s = "Provider=SQLOLEDB;Data  Source=XXXXXXXXXXXXX\YYYYYYYYYY:99999;database=XXXXXXXXXXX;User ID=SVC\XXXXXXX;Password=YYYYYYYYYYYYYYYYYYYY"
ConnectionString_s = "Provider=SQLNCLI11;Data 
Source=XXXXXXXXXXXXX\YYYYYYYYYY:99999;database=XXXXXXXXXXX;User ID=SVC\XXXXXXX;Password=YYYYYYYYYYYYYYYYYYYY"
'SQLSRV32.DLL
'SQLNCLI11'

Connection_o.Open ConnectionString_s

, but it returns invalid parameters or connection string not valid.

[OptionBase1]

Assuming the :99999 in "Source" represents a non-standard port, try replacing the : (colon) with a , (comma)

[dilettante]

Port numbers don't go that high.

[OptionBase1]

Port numbers don't go that high.

...And all of the XXXXX and YYYYY are obviously placeholders for the actual connection information as well...

[techgnome]

User ID=SVC\XXXXXXX;Password=YYYYYYYYYYYYYYYYYYYY"

You have two types of authentication ... Windows, and SQL Login ... One uses the currently logged in user and determines if they have access... that would be a Windows authentication ... the other you supply a user name and a password, and that determines if access is granted ... that would be a SQL Login...
Your connection string indicates a user and password ... which would indicate a SQL Login ... but using "SVC\XXXX" looks like a service user... which wouldn't be a SQL Login ....

Bottom line is it looks like a mismatched connection string. Possibly. Not a lot of info to go on.

-tg

[VBExplorer12]

Thanks everyone,

I think one of my problems is that I don't understand the concept of a Service Account. I tried to look it up on Google, but many of the definitions are circular. I'll try a Youtube video.

Since a Service Account has an id and password, I assumed that it's not Windows authentication. Is that wrong?

I was told that this could only be used programmatically. That you can't log into SSMS with it, even if you login with a different user.

The port isn't 99999, that's just a placeholder. But it IS 5 digits, beginning with 50.

So these are the specs that they gave me:

SERVER/INSTANCE = SSSSSSS\IIIIIII:50999
Service account = SVC\SASASASA
pw = PPPPPPPPPPPP
Database = DBDBDBDB

Is there a provider which is preferable? SQLOLEDB or SQLNCLI11?

[sapator]

I'm septical on the provider. I don't know if it is needed that way or it will bug out. Also not using ports.
Here is 2 datasources I use , one for console app and one for web app:
"Data Source=TestServer;Initial Catalog=Jerkface;User Id=Jer; Password=YesOfCourse;" providerName="System.Data.SqlClient"

web config line:
<add name="C30" providerName="System.Data.SqlClient" connectionString="Data Source=MAINSRV;Initial Catalog=MEGA;Integrated Security=False;User Id=MEGAJERK;Password=YeDreamOn;MultipleActiveResultSets=True;Pooling=False;" />

[VBExplorer12]

Thanks sapator, I tried this approach but it says that "Data source name not found and no default driver specified."


OptionBase1, you're correct about separating the port with a comma, rather than a colon.


In another script, the below works fine. But it's not for a service account.

Connection_o.Open "Provider=SQLOLEDB ; ApplicationIntent=ReadOnly ; Data Source=SSSSSSSSSSS\IIIIII,99999 ; Trusted_Connection=Yes ; Initial Catalog=acctpayable ; Integrated Security = SSPI ;"

For the service account, I tried the one below. It returns an error 'invalid connection string attribute', and says 'These workstations have sessions with open files on this server:'

ConnectionString_s = "Provider=SQLOLEDB ; ApplicationIntent=ReadOnly ; Data Source=SSSSSSSSSSSSS\IIIIIIIIII,99999 ; Trusted_Connection=Yes ; Initial Catalog=DBDBDBDBDBD ; Integrated Security = SSPI ; User ID=SVC\SASASASASASASASASA;Password=PPPPPPPPPPPPPPPPPPPPPPPPP"

[OptionBase1]

I didn't look this up or anything, but it seems wrong to specify "SVC\" in front of the username.

[techgnome]

Thanks sapator, I tried this approach but it says that "Data source name not found and no default driver specified."


OptionBase1, you're correct about separating the port with a comma, rather than a colon.


In another script, the below works fine. But it's not for a service account.

Connection_o.Open "Provider=SQLOLEDB ; ApplicationIntent=ReadOnly ; Data Source=SSSSSSSSSSS\IIIIII,99999 ; Trusted_Connection=Yes ; Initial Catalog=acctpayable ; Integrated Security = SSPI ;"

For the service account, I tried the one below. It returns an error 'invalid connection string attribute', and says 'These workstations have sessions with open files on this server:'

ConnectionString_s = "Provider=SQLOLEDB ; ApplicationIntent=ReadOnly ; Data Source=SSSSSSSSSSSSS\IIIIIIIIII,99999 ; Trusted_Connection=Yes ; Initial Catalog=DBDBDBDBDBD ; Integrated Security = SSPI ; User ID=SVC\SASASASASASASASASA;Password=PPPPPPPPPPPPPPPPPPPPPPPPP"

The ifrst one use the Windows Authentication ... that's where it logs in as the process's user ... which could be a User, OR A Svc Account (at least it should be).

The second one is using SQL Server Authentication ... and unless there's an actual SQL Login called "SVC\XXXXXX" then yeah, it's not going to work.

Really, you should be looking at the Logins on the SQL Server itself, find that user, and see if it's using SQL Authentication (there will be a Login associated with it) or a Windows Authentication (there will be a windows user associated with it).

-tg

[VBExplorer12]

I'm not sure I have permissions to check the logins directly on the server.

Anyway, a .net login seems to indicate this is Windows Authentation


"ConnectionStrings": {
"DefaultConnection": "Server=SSSSSSSSSSSSS.svc.st.gov\\IIIIIIIIII,50999;database=DDDDDDDDDDD;trusted_connection=true" ,
"DefaultConnectionSchemaName": "dbo"
},

Identity: SVC\SASASASASA


I got a little further this time, on the vbs side:

ConnectionString_s = "Provider=SQLNCLI11 ; Data Source==SSSSSSSSSSSSS.svc.st.gov\\IIIIIIIIII,99999;database=DDDDDDDDDDD;Integrated Security = SSPI;DefaultConnectionSchemaName=dbo"

This time, the response wasn't immediate, but it was another Invalid connection string attribute. Probably the DefaultConnectionSchemaName.

[techgnome]

use a single \, not \\ the \\ hapens in the json snip because it needed to be escaped. In the string in your connection string, it does not.

-tg

[VBExplorer12]

No, I'm wrong. It didn't go any further. I put in a double '=' sign in there, and it just took longer to process.

This one at least tells me the service credentials don't belong there, I guess because it's being interpreted as an ordinary user which conflicts with Windows Authentication.

ConnectionString_s = "Provider=SQLNCLI11 ; Data Source=SSSSSSSSSSSSS.svc.st.gov\IIIIIIIIII,99999;database=DDDDDDDDDDD;Integrated Security = SSPI;User ID=SVC\SASASASASASASASASA;Password=PPPPPPPPPPPPPPPPPPPPPPPPP"

Returns
"Login failed for user 'SVC\Dddddd2' (Ddddd2 is my identity on the PC where I'm running the VBScript)."

So, I still don't understand - and can't find on the web - how to log into Sql Server with a Service Account. I have no problem logging in, any other way.

[VBExplorer12]

use a single \, not \\ the \\ hapens in the json snip because it needed to be escaped. In the string in your connection string, it does not.

-tg

Yep, I know techgnome. I had accidentally copied the double-slash from the .net string.

[VBExplorer12]

I also tried RunAs with the script, and it says that the user has not been granted the requested logon type at this computer.

I also tried the same thing on a remote PC, and got the same result.

[techgnome]

No, I'm wrong. It didn't go any further. I put in a double '=' sign in there, and it just took longer to process.

This one at least tells me the service credentials don't belong there, I guess because it's being interpreted as an ordinary user which conflicts with Windows Authentication.

ConnectionString_s = "Provider=SQLNCLI11 ; Data Source=SSSSSSSSSSSSS.svc.st.gov\IIIIIIIIII,99999;database=DDDDDDDDDDD;Integrated Security = SSPI;User ID=SVC\SASASASASASASASASA;Password=PPPPPPPPPPPPPPPPPPPPPPPPP"

Returns
"Login failed for user 'SVC\Dddddd2' (Ddddd2 is my identity on the PC where I'm running the VBScript)."

So, I still don't understand - and can't find on the web - how to log into Sql Server with a Service Account. I have no problem logging in, any other way.

You use this:
Integrated Security = SSPI;
OR this
User ID=SVC\SASASASASASASASASA;Password=PPPPPPPPPPPPPPPPPPPPPPPPP

Not both...


As I think about it more ... I don't think you can use the service login ... the service logins are local to the machine if I remember right. It's the context the under which services on that computer will run. And I don't think they are transferrable. The reason I'm thinking this is is because they're not an AD account ... which is what SQL Server sees (besides the accounts local to that server).

maybe tilting windmills here...

-tg

[VBExplorer12]

Yep, I understand techgnome. I thought that maybe the service account credentials were exceptional somehow, especially since the .net version has trusted_connection=true.

This obviously has to be executed via RunAs.

I'll ask my manager for a workstation where the service credentials can be run. I think he told me that I was in the group, but they are wrong about this many times.

[OptionBase1]

I think your over-exuberant placeholder modification of the connection string is really, really muddying the waters here.

Earlier you said this:

"Login failed for user 'SVC\Dddddd2' (Ddddd2 is my identity on the PC where I'm running the VBScript)."

But then you keep making mention of trying to log in with a service account. So which is it? Are you using your own Windows user account credentials in the connection string?

And it still isn't clear what the "SVC" prefix you are using for the credentials is attempting to represent here. Is it the host name of the server that you are connecting to? Is it your local workstation name? Is it the NetBios name of the Active Directory domain that everything is joined to? Is it the SQL Server instance name?

While it is possible that the root cause is a lack of permissions, I'm not convinced that is the case. If you could provide more specific details answering the questions I pose above, then you might get pointed in the right direction.

Good luck.

[VBExplorer12]

I think your over-exuberant placeholder modification of the connection string is really, really muddying the waters here.

Earlier you said this:



But then you keep making mention of trying to log in with a service account. So which is it? Are you using your own Windows user account credentials in the connection string?

And it still isn't clear what the "SVC" prefix you are using for the credentials is attempting to represent here. Is it the host name of the server that you are connecting to? Is it your local workstation name? Is it the NetBios name of the Active Directory domain that everything is joined to? Is it the SQL Server instance name?

While it is possible that the root cause is a lack of permissions, I'm not convinced that is the case. If you could provide more specific details answering the questions I pose above, then you might get pointed in the right direction.

Good luck.

SVC IS the first 3 letters of the service login account. Ddddd2 is my local account.

At any rate, I got an answer. It woudn't have been possible to do this locally.


"I did talk to XXXX about this issue. Per XXXX, you won’t be able to connect to the SQL DB directly from SSS using a windows service account. For .Net apps, we are connecting to DB via IIS app pool. Is there anything similar in SSS? If not, we may have to check with DB team for a local DB account."

The weblogic server that connects is running from a linux server.

The person who asked me about this originally, is building a Java app and can't figure out how to connect to the database with the service account provided. I told them I would try from a different environment - VBScript - but of course was unsuccessful. There was also a .net app that connected successfully, but of course it's not going to work from our angle.


Thanks everyone for trying to help. The java developer is pointed in the right direction. Resolved.