Create ODBC connection to SQL 2012 Express
Hello,
I setup SQL Express 2012 onto a Windows Server 2008 R2 system. When I installed SQL Server, I installed it in Mixed Mode, and I added a few network users to be admin. I also created an sa account and password. The install completed and I can create a database.
From a Windows 7 workstation, I am trying to create a ODBC connection and filedsn to the server. The server is found. But, it will not let me connect. When I try to connect, I get the following error:
Connection failed
SQLState: 01000
SQL Server Error: 10060
SQL State 08001
SQL Error 17
Server does not exist or access denied.
I thought this might be a firewall issue so I turned off the firewall. The server is part of a workgroup so I enabled the following ports: 1433 and 1434 and 443.
What else could be causing me from not being able to create an ODBC DSN connection to the SQL 2012 Express server?
Thanks
SQLError.png
I setup SQL Express 2012 onto a Windows Server 2008 R2 system. When I installed SQL Server, I installed it in Mixed Mode, and I added a few network users to be admin. I also created an sa account and password. The install completed and I can create a database.
From a Windows 7 workstation, I am trying to create a ODBC connection and filedsn to the server. The server is found. But, it will not let me connect. When I try to connect, I get the following error:
Connection failed
SQLState: 01000
SQL Server Error: 10060
SQL State 08001
SQL Error 17
Server does not exist or access denied.
I thought this might be a firewall issue so I turned off the firewall. The server is part of a workgroup so I enabled the following ports: 1433 and 1434 and 443.
What else could be causing me from not being able to create an ODBC DSN connection to the SQL 2012 Express server?
Thanks
SQLError.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try using the IP address as the host name and see if it shows up. If it doesn't then maybe it's not 'exposed' to external connections.
Is the SQL Browser Service running?
If it isn't then you probably have to go into the SQL Configuration manager and configure for remote access.
If it isn't then you probably have to go into the SQL Configuration manager and configure for remote access.
ASKER
The Sql configuration manager shows that the SQL SErver and SQL Server Browser is running. But, the SQL Server Agent is not running even though it is set to automatic. When I try to run the service, it returns:
"The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details."
The application log shows: OpenSQLServerInstanceRegKe
Searching for this error, it seems that I may need to use a different account to start this service other than the built in Network Service account. So, I tried a network account that I created for the SQL service (an account with domain admin rights), and the service still will not startup. when I tried to use a different account, such as a domain administrator to start the service, I received an error that says:
"The process terminated unexpectedly." [0x8007042b]"
Is this SQL Sever Agent not running a problem?
At the workstation where I am trying to make a connection to this server, I am walking through the ODBC Data Source Administrator. I can choose the server using an IP address and the named instance and press next. When I get to the "Change the default database to" screen choice, I can select (default) or press the down arrow to choose a database. When I press the down arrow to choose a database it spins for a few moments and returns with the following error:
Connection failed:
SQLState: 08001
SQL Server Error: 10060
[Microsoft][ODBC Driver 11 for SQL Server] TCP Provider: A connection attempt failed because the connected party did not respond after a period of time, or established connection failed because connected host has failed to respond.
Connection failed:
SQLState: HYT00
SQL Server Error: 0
[Microsoft][ODBC Driver 11 for SQL Server] Login timeout expired.
Here is another
"The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details."
The application log shows: OpenSQLServerInstanceRegKe
Searching for this error, it seems that I may need to use a different account to start this service other than the built in Network Service account. So, I tried a network account that I created for the SQL service (an account with domain admin rights), and the service still will not startup. when I tried to use a different account, such as a domain administrator to start the service, I received an error that says:
"The process terminated unexpectedly." [0x8007042b]"
Is this SQL Sever Agent not running a problem?
At the workstation where I am trying to make a connection to this server, I am walking through the ODBC Data Source Administrator. I can choose the server using an IP address and the named instance and press next. When I get to the "Change the default database to" screen choice, I can select (default) or press the down arrow to choose a database. When I press the down arrow to choose a database it spins for a few moments and returns with the following error:
Connection failed:
SQLState: 08001
SQL Server Error: 10060
[Microsoft][ODBC Driver 11 for SQL Server] TCP Provider: A connection attempt failed because the connected party did not respond after a period of time, or established connection failed because connected host has failed to respond.
Connection failed:
SQLState: HYT00
SQL Server Error: 0
[Microsoft][ODBC Driver 11 for SQL Server] Login timeout expired.
Here is another
The SQL Server Express agent is a specialized engine that is designed to fire up and do some basic stuff during the SQL Service statup. But it automatically shuts off afterward.
As for the service accounts: DO NOT use a domain admin account. It should be a regular domain account and then the easiest way is a server local admin account. Even that has issues but is closer to a secure setup.
In the configuration manager make sure that the TCP has priority. Preferably turn off Named Pipes.
As for the service accounts: DO NOT use a domain admin account. It should be a regular domain account and then the easiest way is a server local admin account. Even that has issues but is closer to a secure setup.
In the configuration manager make sure that the TCP has priority. Preferably turn off Named Pipes.
When I try to connect, I get the following error:
SQL Server installed as named instance, so you need to connect to YourComputername\SQLExpres
But, the SQL Server Agent is not running even though it is set to automatic.
SQL Server Express does not support SQL Server Agent.
SQL Server installed as named instance, so you need to connect to YourComputername\SQLExpres
But, the SQL Server Agent is not running even though it is set to automatic.
SQL Server Express does not support SQL Server Agent.
ASKER
I decided to install SQL Server 2012 and it works better. On the server, I am able to connect via ODBC and also view the tables with management Studio.
From the workstation, however, I still cannot connect via ODBC or even with the management studio loaded. I am not sure why and thought it might be the firewall.
I disabled the firewall service but it did not make a difference. For now, I might have to give this a rest until I have more time to test it. I was able to get done what I needed to get done locally on the server.
From the workstation, however, I still cannot connect via ODBC or even with the management studio loaded. I am not sure why and thought it might be the firewall.
I disabled the firewall service but it did not make a difference. For now, I might have to give this a rest until I have more time to test it. I was able to get done what I needed to get done locally on the server.
Hi,
Have you checked via Configuration Manager that tcp is enabled. Some editions such as early editions of SQL Express install with only shared memory network protocol enabled. That is, can connect on the machine, but can't connect from other machines.
Regards
David
Have you checked via Configuration Manager that tcp is enabled. Some editions such as early editions of SQL Express install with only shared memory network protocol enabled. That is, can connect on the machine, but can't connect from other machines.
Regards
David
ASKER
The bizarre resolution to this is that I had to create a server alias and then I was able to get in.
ASKER