We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL Connection Attribute error

Medium Priority
59 Views
Last Modified: 2020-06-18
I have 2 sql servers running on my Server, 2008 and 2016,  I connect to the 2008 on a regular basis for development.  In am now trying to connect to the 2016 server and am getting an "invalid connection string attribute".  When I connect to the 2008 server with the server manager, the server name is "(local)".  When I connect to the 2016 server, the server name is "ROONEY01\SERVER02.  

When I connect to the local server my connection string is

Provider=SQLOLEDB.1;Persist Security Info=True;User ID =xxxx;Password =xxxx;Database =FIFDB;Data Source =ROONEY01

Shouldn't  the connection  string for 2106 server be

Provider=SQLOLEDB.1;Persist Security Info=True;User ID =xxxx;Password =xxxx;Database =FIFDB;Data Source =ROONEY01\SERVER02

The Userid and Password are the same for both.
Comment
Watch Question

CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Yes, the DB is servername\instance

Your issue is you are using SQL 2008 to connect to 2016.
Usually, the tools, libraries will connect to older versions and one version newer ...

You need to use an updated version of sqlncli to use to connect to the SQL 2016.
Rob RietowOwner

Author

Commented:
Thanks for your reply.
You have sent me into an area I known nothing about.  What I gather is that there is some software/driver that needs to be installed on my Client in order to connect to the 2016 server.  There are no sqlncli files on my client.  I see sqlcli10 and sqlcli11 files on my Server. I also see a sqlncli.msi file there. What do I need to do to get my Client upgrade so I can connect to both the 2008 and 2016 servers? And I have a number of customers that run this software.  I assume what ever I do on my client must be done on theirs as well.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Test using odbcad32 (based in your client app 64 or 32 bit) to setup a DSNnusing SQL server as the data provider.
And see if that changes the interaction, your client application connection string will need to use the DSN based SQL connection string
Each version of SQL server comes with it's own SQL native client library in various versions 32 and 64 bit.
Are you able to use ssms on the 2098 server to connect to the SQL server 2016?

Sounds like you have SQL native client libraries for SQL server 2008 and 2012 (10,11)

What DB mode (options) is the server on the 2016 operates in? Does the application you use incorporated the newer SQL native client libraries?
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Rob RietowOwner

Author

Commented:
None of the 2 collapsed comments  seemed to address the issue. Do I need to install a sqlncli.msi  that is on the Server onto my client ?  Will this interfere with my connecting to 2008 after I 'install' this? And once installed, if that is what I am to do, what is the connection string?
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
The install is cummulative you would have sqlserver three versions

Test it , do you use ssms to access each database?

You are using sqloledb.1

Consider testing using aDSN typeofconnection where it uses SQL server of the correct version..

Try the other connection strings

In what context is the connection used, .net web application, ..
Rob RietowOwner

Author

Commented:
You seem to be moving away from the subject.  You stated "You need to use an updated version of sqlncli to use to connect to the SQL 2016. "
I am trying to understand what I need to do get this done.  There is a sqlncli.mis on the server.  Do I install this on the client?  If so, what would change in the Connection String?  I am using Delphi 2007 and ADO.  
I believe you are right that I need a newer version of something to connect to 2016.  I am just trying to get there. I need to be able to connect to both the 2008 SQL server and the 2016 SQL server.

CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
I am not moving, I am making educated guesses as to what might be going on.
the attribut error means a parameter I your connection attempt is not valid.
And while this connection format worked for 2008, it as I noted it might. Not work with newer versions.

Use the odbcad32 to see how may drivers for SQL server do you have 10. For SQL 2008, 11 for SQL 2012...

If you gave SQL 2012 ncli it should enable you to access DSL Server 2016
Sqlncli for 2016! Version 13..

Delphi, 32 bit? You would need the 32 bit version
Use the c:\windows\syswow\odbcad32.exe see which driver versions you have installed.

The DSN based connection string is a way to manage, control the tests.
I posted a link as an example that does not include a provider
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
See https://www.microsoft.com/en-us/download/details.aspx?id=52676

Download includes many tools, shared objects etc.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Here is another reference deals with sqloledb doing to Ned.
Note your provider uses sqloledb.1

See if the following is an option
https://docs.microsoft.com/en-us/sql/ado/guide/appendixes/microsoft-ole-db-provider-for-sql-server?view=sql-server-ver15
Rob RietowOwner

Author

Commented:
Lets go back to here: You said:
Yes, the DB is servername\instance
                                   
 Your issue is you are using SQL 2008 to connect to 2016.
Usually, the tools, libraries will connect to older versions and one version newer …
                 
You need to use an updated version of sqlncli to use to connect to the SQL 2016. 


There is an SQLcnli.mis that was installed on the Server when I installed 2016. Is this what I need to install on my workstation?

I found the odbcad32 in  the syswow64 directory.  The only pertinent entry I found was  HHSDB - 32/64 bit - SQL Server

CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
within the odbc interface you can add system DSN's as needed.
check which drivers are available to you to use

try this
add DSN using sql server version 10 (Sql 2008) to SQL 2016
add dsn1 using sql server version 11 (SQL 2012) to SQL 2016
DSN setup to use SQL login. do not save the login when creating the DSN

your connection string from https://www.connectionstrings.com/dsn/
DSN=DSN;UID=xxx;PWD=xxxx
see if you can connect.
DSN=DSN1;UID=xxx;PWD=xxx
and try it this way

In a way this simplifies your coding while complicating the install side that requires the creation the applicable DSN's.
Rob RietowOwner

Author

Commented:
What I see on adding is
ODBC Driver 11 for SQL Server
ODBC Driver 13 for SQL Server
SQL Server
SQL Server Native Client 11.0
Not sure how that correlates to what you have above.  

CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Try adding a DSN using SQL server native 11 for the SQL 2016and see if you can access the data ... Work within.
Then try using odbc diver 13 for the DSN and repeat.

If working ,
You can use the registry to look at the odbc.ini syswow64 for 32 bit to identify which provider you need to use in your connection string context if you do not want to setup DSNs.
Rob RietowOwner

Author

Commented:
I successful set up DSNs for the 2008 server using both Native and ODBC 13.  However for the 2016 Server "ROONEY01\SERVER02", the test failed for both

What value in the ODBC.ini  is the Provider?


CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
The registry regedit hkey_local_machine\software\microsoft\indows\currentversion\odbc
Software\syswow64\microsof\....
You need to identify the provider referenced there to use in your connection string.

Note your connection string uses sqloledb.1

Are you able to remotely connect to the SQL 2016 using ssms?
When setting up SQL server, did you add firewall rules to allow TCP/ip connection, is that protocol enabled on the server. Are they running on the same system or are they running on two separate systems?
Rob RietowOwner

Author

Commented:
Can't find any of the entries I made or sqloledb.1 in the registry entry above.  All the DSN entries I made are in HKEY_USERS.. Tried to start odbcad32 as admin and got a missing dll error.
Don't know what ssms is.
 Both SQL servers on  the same Server
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Is the SQL server 2016 instance configure to listen on TCP/IP?
Compare the configuration of the SQL server 2008 and 2016 it I would be addressable by instance

Are you using ssms to connect to both? Can you confirm that you can.
Rob RietowOwner

Author

Commented:
Do not  know what ssms is.

What do you mean "Addressable by Instance"?  in the DSN I referred to is as ROONEY\SERVER02 which is its name in the Server Manager.

Listening on TCP/IP. How do I tell?
Rob RietowOwner

Author

Commented:
SSMS. Oh. Management Studios.  Yes.  I have 2, one for 2008 and 1 for 2016.  They both access the appropriate server.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
You should have an SQL server 2016 surface configuration tool similar to the SQL server 2008 one, check

The \server02 in rooney is the instance
The way it goes servername\instancename

If you do not include an instance, that means it is using the Defaul instance.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Try usin the 2016 to access the 2008
If you install the ssms from the 2012 version it shoukd be able to connect and interact with both.
Rob RietowOwner

Author

Commented:
I can connect to both the 2008 SQL Server and the 2016 SQL server with the 2016 SSMS
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Try the following connection string
"Provider=sqloledb.1;Data Source=rooney01\Server02;"initial catalog=FIFDB;User ID =xxxx;Password =xxxx;

This a from the link provided earlier from Microsoft. Distinction deals with using initial catalog versus database. And grouping the provider and data spurce.
Rob RietowOwner

Author

Commented:
"...Server doesn't exist or access denied"

CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
confirm user ID and password are valid for the database you want to access on that DB server
at least you are not getting attribute related errors. for now.
Rob RietowOwner

Author

Commented:
I went to the Server and created a DSN with Native 11 and Windows logon to ROONEY01\SERVER02.  This connected. I then created another DSN with Native 11 and User/Password logon to ROONEY01\SERVER02.  This would not connect.  I went to the SSMS and created a new user and password.  I tried this User/Password in the DSN and it would not connect.  I lost know..
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
what is the error? double check whether the user you created has the right access to the DB you want it to connect to.

using ssms, are you able to connect to the instance using the sql login with this username/password??

i.e. you create a login myuser with password mypassword.
using ssms are you able to login into the ms sql 2016 using ssms and then look at the db?
If you can not access the DB from SSMS, you need to make sure the database security, users has the user with a right to the DB to do what you need, db_reader. db_writer, etc.
once you can access and query the DB using ssms and the sql login, try the DSN;'s again.
Rob RietowOwner

Author

Commented:
I have access to 2008 and 2016 via SSMS.   When I click on the Icon SSMS starts.  Both say ROONEY\Administrator. I can look at everything in both servers. I can do Queries in both SQL Servers.  The login rights look the same for Administrator.
So when I create the DSN with Windows Authentification, it connects.
When I take that same DSN  and use User/Password and use the Administrator User/Password. the connection fails.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Switch in ssms from Windows logon to SQL login, then see if you can login using the ssms and SQL login.

When setting up the DSN, you can test the credentials you are passing ....
The password is case sensitive.
Rob RietowOwner

Author

Commented:
Please read the message above you last posting.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
I did.

Pkease login using SQL credentials and not Windows authentication when using ssms.

I have access to 2008 and 2016 via SSMS.   When I click on the Icon SSMS starts.  Both say ROONEY\Administrator. I can look at everything in both servers. I can do Queries in both SQL Servers.  The login rights look the same for Administrator.
Rob RietowOwner

Author

Commented:
I am not able to create a login (User/Password) that will log into Server02.  Keep getting 18456
Rob RietowOwner

Author

Commented:
Error says something about SQL Server set up for Windows Authorization only.  Where do I change that?
Rob RietowOwner

Author

Commented:
Change setting in server to allow Windows and SQL authorization.  Was able to logon the with the user/password I created.  Retrying the DSN
Rob RietowOwner

Author

Commented:
DSN connected
Rob RietowOwner

Author

Commented:
So now I am back to the connection string. The SQLOLEDB.1 gives me that attribute error.  So what should I use as a Provider or how do I use DSN in the connection string?
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Why not stick with the DSN?
Try the option from
https://www.connectionstrings.com/sqlxml-4-0-oledb-provider/using-sql-server-native-client-provider-sqlncli11/

Try using initial catalog instead of Database i  your sqlOLEDB.1

in the DSN
regedit note if you are lookin in HKEY_current_user this means you created a user DSN
for a system DSN the path is
HKLM\ for system
HKCU for user

software\
wowsys64 for 32 bit version
\microsoft\currentversion\odc\odbc.ini
a list of DSN
see what they list for provider.

se eif you use sqlncli11 what happens...

you can export the DSN and import (merge) as part of the install..
Rob RietowOwner

Author

Commented:
I now can connect to 2008 from my client machine with SQLNCLI11.  So I can now use SQLNCLI11 as a Provider.
And I have build a DSN which connects to my 2008 Server with SQLNCLI11 with User/Password.
And I can create a DSN on my Server which connects to my 2016 server with SQLNCLI11 with User/Password

But, I cannot build a DSN on my Client machine which will connect to my 2016 Server with SQLNCLI11 and User/Password.  I have created a username on my Server which matches the SQL User/Password.

So I am still missing a piece which allow the connection from my Client machine to the 2016 SQL Server
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Double check the SQL server 2016 configuratio settings if you have TCP/ip enabled where youhave two sqlserver Different instances potentially listening inthe same port 1433...

When using ssms, check options and see if you use TCP/ip or other means to connect.
Rob RietowOwner

Author

Commented:
Where do I find that setting?
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Tlook ithe start menu all programs Microsoft sql. server2016
SQL server configuration manager...

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-or-disable-a-server-network-protocol?view=sql-server-ver15#SSMSProcedure
Note you are configuring the server section, the client side has all options enabled by default.
The server side,commonly only has shared memory access enabled .
Rob RietowOwner

Author

Commented:
TCP/IP was not enabled, so I enabled it.  However I don't know what settings to give it.  I see IP1 thru IP7 and IPAll.
For both servers, all the individual IP's are Active but not Enabled.  The Port for Rooney01 is 1433.  Can I use the same port for Server02?  The will not be accessed at the same time.  Any other settings required?

BTW, thanks for sticking with me on this!
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
By default a server listens on all IPs. that is the difficulty let it listen on all Ip on port 1433 and see if the two can be differentiated because of rooney01 and rooney01\server2.
Rob RietowOwner

Author

Commented:
So what port should I use for Server02?  Still not connecting. 'Error locating Server\Instance'.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
How many IPs on the Lan does this system have

You could set it to 1430 and then add a firewall rule on the incoming side to allow aconection on port 1430, then you would need to add ,1430 to the rooney01\server2,1430 to tell it to connect on a different port.
Rob RietowOwner

Author

Commented:
Set all the IP's on Server02 to 1430.  Set Rule to allow port 1430. Still not connecting. Still not connecting. Do I also need a different IP address?  I have a hub, so many are available.  
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Nto the rooney01\server2,1430
To indicate it needs to use a port other 1433
Rob RietowOwner

Author

Commented:
I do not under stand what you said.​​​​
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Please post the new connection string for the SQL 2016 server.

The part I am interested in and the part that should resolve your issue is whether in the connection string you indicate that to connect to the sql 2016 server the connection requires that port 1430 be used and not the default port 1433. This is achieve by adding ,1430 to the Data source

Provider=SQLOLEDB.1;Persist Security Info=True;User ID =xxxx;Password =xxxx;initial catalog =FIFDB;Data Source =ROONEY01\SERVER02,1430

Open in new window

Rob RietowOwner

Author

Commented:
I'll try that, but I have been able to connect with the a DSN from my workstation.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
ok, then use the DSNs to connect to both 2008 and 2016?
In the DSN you configured the ports?
Rob RietowOwner

Author

Commented:
This dialog did not solve my issue..
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
What is the connection string?
Is SQL 2016 supported by Delphi in use?
Owner
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.