Trouble creating ODBC connection for local SQL Server DB (MS SQL Server 2012 Express)

The difficulty is likely based on e choice in the connection that you make dealing with access to the server.
Which driver are you selecting! What authentication method are you selecting?
Did you configure Tcp/ip based access to the sql server instance?
What is he error that you are getting when attempting to connect?
Does the odbc/DSN connection that you made pass the test?

There is not enough info in your post to definitively know what might be going on.

ODBC Data Source Administrator dialog:
System DSN {Add}
(2 options appear:
 SQL Server v6.01.7601.17514 SQLSRV32.DLL32dll 11/20/2010
 SQL Server Native Client 11.0 2011.110.2100.60 SQLNCLI11.DLL 2/11 2012
(I select the "Native Client", as it seems to be the only option that could work with 64-bit MS SQL Server Express 2012)


Create a New Data Source to SQL Server dialog:

Name qwerty
Description qwerty
Which SQL Server do you want to connect to?  Server: {my name for my pc}

How should SQL Server verify the authenticity of the login ID?
(I have tried both Integrated Windows authentication and with SQL Server authentication, supplying the username and password that worked in the remote version of the database)

Change the default database to: ___________________
 (I have tried leaving it blank, and setting it to be the name of the database)

Microsoft SQL Server Native Client Version 11.00.2100
Running connectivity tests

Named Pipes Provider: Could not open a connection to SQL Server [2].[Microsoft][SQL Server Native Client 11.0]Login timeout... (see screenshot, attached)
ODBC-failed.PNG

You have a named instance likely server_systemname\sqlexpress

use the sql server configuration tool to see which options are enabled for the server instance shared, named pipes, tcp/ip usually if I am not mistaken, the only one that is enabled by default is the shared. Enable all, you would need to restart the service (mssql$instancename in services)

Then try again.
If needed, you may have to adjust the advanced firewall settings, to allow port 1433 through the firewall (though if you are running this on your own workstation, it should not matter, but something to consider should this attempt fail.

You could before attempting the above, retry the qwerty setup again using the {servername of my pc\instancename} for the server to which the odbc is connecting.

Thanks Arnold,

Using the SQL Server Configuration Tools/Configuration Manager:

SQL Native Client 11.0 Configuration/Client Protocols:
 Shared Memory, TCP/IP, and Named Pipes all enabled.

SQL Server Network Configuration:/Protocols for SQLEXPRESS:
 Shared Memory: Enabled
 TCP/IP: Disabled
 Named Pipes: Disabled.

(I thought I was only using the "SQL Native Client 11.0", but will enable the TCP/IP and Named Pipes for SQL Server Network Configuration, then stop (if necessary) and restart the Service (or maybe reboot the PC!) and try again.

I stopped and started the service SQL Server (SQLEXPRESS), and after that went back into ODBC and the name of my computer is no longer in the drop down list. I'm gonna try a reboot and see if the Service starts normally.)

You do not need to reboot, double check the service is running, try (local\sqlexpress)

Check whether you have browsing service running as well.

MSSQL$SQLEXPRESS Running
SQLAgent$SQLEXPRESS Stopped
SQLBrowser Stopped

SQLBrowser is what helps you see what servers are available (discovery service)..
you can always enter local\sqlexpress in the DSN setup and see what happens.

Double check your server configuration. Is it set to mixed authentication or is it only windows authentication.
I do not believe you can use mysql workbench in windows integrated connection mode, but it should work using sql authentication.

presumably you have ssmse installed that you can use to check on the server setup for authentication.

Thanks so much for trying, Arnold. I have to crash, but will try again tomorrow. The final thing(s) I tried:

I did go into SSMSE and saw that authentication was set to Windows authentication (and was working.) When I tried the login and password (as was correct on the ONLINE version of the same database in a shared hosting environment), it failed with error 18456. So, I've got to get SQL authentication working, so that MySQL Workbench will work.

Will look at 18456 tomorrow: http://sqlserver-help.com/tag/error-18456/

Also:

I got SQLBrowser Running

SQL Agent appears as if it can be started, then seems to time out.
SQLAgent$SQLEXPRESS Stopped

I tried local\sqlexpress in the DSN and got (see attached image):

Within ssmse properties of the named database server listed there will let you change the authentication method to allow both mixed mode.

OK, I did find it (hidden in) SSMSE, and changed it to mixed. Restarted the Service. Same error (18456) with attempting to login using SQL Server authentication.

Are you entering or selecting the server\instance from the list

servername\sqlexpress as the server. Mixed mode as the selection. sa and password.

When in workbench, what are you specifying.  Double check.


I am not sure what you are doing, you could export the data from your MS SQL in a CSV format
Then you can load the data
You could also export the data in XML and import the data into mysql.

You have to make sure the ODBC you are using on the workbench side works with sql server 2012.


the error you show indicates it can not locate the server/instance.

check the TCP/IP settings in the configuration to see what port it is using with if different then 1433, you...

Check to make sure your system DSN is setup and passes the connection test

2014-07-08 05:35:48.850 Logon        Login failed for user ‘xylem_dennis’. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

 Error: 18456, Severity: 14, State: 5.

I assume that because this was in a shared hosting environment, the user name got prepended with xylem_ (first 5 characters of the site's URL, similar to what Linux-based hosting does.) But the user certainly does exist and has been the user that does all of the connections to run a forum online, using the online SQL Server database.

What I don't know is if a BAK file gets only the user names and not the login credentials. If so, I don't see where in SSMSE that I can even check the username/password credentials - but the error State 5 doesn't say the password is wrong it says the user doesn't exist.

The user xylem_dennis is shown in SSMSE, under the database, Security/Users. This is the user that has been the main connection to the online database for 7 years.

Now, I'm seeing something (searching around online) that says I need to create a login and that it is a separate process from creating a user. Sheesh. Microsoft drives me crazy. Hunting for how to create logins now...

At this point (of frustration), I think I have no SQL Server authenticated logins. I only have Windows authentication, because no logins have been created. I have no idea where to create logins that will become SQL Server authenticated logins, or how to assign database permissions or ownership (if necessary to use the login.)

I am brand new to SQL Server Management Studio 2012 Express. The last time I used SSMS was probably 6 or 7 years ago, and may have been SSMS 2000 or SSMS 2005, I can't remember. I only used it to manage a remote server on a shared host, so I have never gone through the pain and agony (or maybe it's really easy, just terribly obfuscated) process of getting a local SQL Server database working. I'm guessing that the typical guy like me (who doesn't quite know enough about SSMS to be dangerous) uses Windows authentication, and has never dealt with the issue of getting mixed authentication (actually, I think that used to be the default way the old version of SSMS that I had worked), or specifically of getting SQL Server authentication working.

If I was just using SSMS to administer a remote database, I would not worry about it. But, since I want to abandon the SQL Server ship and port/migrate to MySQL, then I have to get this working. At least, I need MySQL Workbench to be able to connect to my local database, and it appears to me it only makes that connection through ODBC (and so far, I have no ODBC connection that passes its test.)

Are you entering or selecting the server\instance from the list servername\sqlexpress as the server.

selecting, via dropdown

Mixed mode as the selection. sa and password.

mixed does not come up as an option, either in the Windows ODBC configurator, or in SSMS . I have not used sa (don't know its password if I were to use it, I saw "Password123" online somewhere as the supposed default)

When in workbench, what are you specifying.  Double check.

I think MySQL Workbench is not the issue - the issue is that I cannot figure out how to make an ODBC connection, using SQL Server authentication (or mixed), that works. Once I have that part done, I suspect Workbench will have no problem.

I am not sure what you are doing, you could export the data from your MS SQL in a CSV format
Then you can load the data
You could also export the data in XML and import the data into mysql.

This is a 400MB database with a hundred tables. I'd really prefer to use the migration wizard - if I can get it to work - and I believe that all I need is a working ODBC driver that connects to the local SQL Server database.

You have to make sure the ODBC you are using on the workbench side works with sql server 2012.

I have to have a working ODBC to even test it ...but I could test it in a heartbeat if I could get a working ODBC connection.

the error you show indicates it can not locate the server/instance.

Yes, if I try SQL Server authentication. But it can find the server/instance if I leave it set to Windows authentication.

check the TCP/IP settings in the configuration to see what port it is using with if different then 1433, you...

In SQL Server Configuration Manager, SQL Native Client 11.0 Configuration, Client Protocols, TCP/IP iEnabled and when I right click TCP/IP and select Properties, I see that the default port is 1433.

While in SQL Server Configuration Manager, if I examine SQL Server Services, I see
SQL Server (SQLEXPRESS) Running
SQL Full-text Filter Daemon Launcher (SQLEXPRESS) Running
SQL Server Reporting Service (SQLEXPRESS) Running
SQL Server Agent (SQLEXPRESS) Stopped
SQL Server Browser (SQLEXPRESS) Stopped

Check to make sure your system DSN is setup and passes the connection test

It does not. This is the snafu.

Sorry I abandoned this question, but I did truly abandon trying to do the migration myself.

Arnold, thank you so much for the time you put into this with me. You deserve the points, and more.