Link to home
Start Free TrialLog in
Avatar of Rob Rietow
Rob Rietow

asked on

SQL Connection Attribute error

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.
Avatar of arnold
arnold
Flag of United States of America image

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.
Avatar of Rob Rietow
Rob Rietow

ASKER

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.
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?
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?
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, ..
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.

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
See https://www.microsoft.com/en-us/download/details.aspx?id=52676

Download includes many tools, shared objects etc.
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
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

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.
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.  

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.
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?


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?
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
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.
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?
SSMS. Oh. Management Studios.  Yes.  I have 2, one for 2008 and 1 for 2016.  They both access the appropriate server.
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.
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.
I can connect to both the 2008 SQL Server and the 2016 SQL server with the 2016 SSMS
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.
"...Server doesn't exist or access denied"

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.
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..
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.
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.
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.
Please read the message above you last posting.
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.
I am not able to create a login (User/Password) that will log into Server02.  Keep getting 18456
Error says something about SQL Server set up for Windows Authorization only.  Where do I change that?
Change setting in server to allow Windows and SQL authorization.  Was able to logon the with the user/password I created.  Retrying the DSN
DSN connected
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?
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..
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
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.
Where do I find that setting?
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 .
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!
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.
So what port should I use for Server02?  Still not connecting. 'Error locating Server\Instance'.
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.
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.  
Nto the rooney01\server2,1430
To indicate it needs to use a port other 1433
I do not under stand what you said.​​​​
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

I'll try that, but I have been able to connect with the a DSN from my workstation.
ok, then use the DSNs to connect to both 2008 and 2016?
In the DSN you configured the ports?
This dialog did not solve my issue..
What is the connection string?
Is SQL 2016 supported by Delphi in use?
ASKER CERTIFIED SOLUTION
Avatar of Rob Rietow
Rob Rietow

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial