• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2284
  • Last Modified:

Create ODBC link to SQL EXPRESS 2008

I recently downloaded SQL Server Express 2008 but cannot connect to it either through Access where I tried to upload my access database or through creating an ODBC driver. Both the Access and SQL Server 2008 are located on my hard drive. I did not create any Sql log-in or SQL passwords so both should be using my network information. I receive the same messages.
Connection Failed:
SQLState: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.

I have no problems connected to SQL server on remote servers so I think I am missing a configuration setting setup or a DLL?

Thank you
0
audreymjhha
Asked:
audreymjhha
  • 6
  • 5
1 Solution
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Express installs itself usually as a Named Instance, .\mssqlexpress or alike. Did you check that?
0
 
audreymjhhaAuthor Commented:
The name that comes up when I execute Microsoft SQL Server management is \computer name\SQLExpress1. I tried to use that and I receive a separate error.
SQL State: '28000'
SQL Server Error: 18452 or

SQL State: '28000'
SQL Server Error: 18456

I enabled the sa account. Everything is on my computer and I am a system administrator. When I execute the SQL Server management, the authentication is Windows Authentication. The username is my domain\username and the password is blank. The password checkbox is blank. I have tried everything including \\domain\username with my domain password and without as well as using the sa account with the password I gave it. Help!!

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
If the authentication is (exclusively) Windows Authentification Mode, than you will not be allowed to provide a username and password. Use "trusted", that is the same as Windows Auth. Do not provide any user information.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
audreymjhhaAuthor Commented:
Hi,
   I installed SQL Express 2008 with Windows Authentication. How do I successfully create an odbc driver. I did try to upload from access using "trusted" but that failed. I keep receiving the same messages that "I am coming from an untrusted domain and cannot be used with windows authentication. I only have one domain and I am a domain administrator and everything is on my PC. Why cannot I not connect. If I change to mixed mode I can connect through the sa account. Also, I copied a database called "Countries" to another database and called it "Countries 3". I created a back up of countries. I tried to right click on "Countries 3" and restore the "Countries" backup but it denied me since it is different. Since the "Countries 3" is an exact replica - why would I fail? It gives me the option to select the database to restore from. What would be the easiest way to create a replica of a database.

Thank you
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
To much mixed up here.

You can't restore the backup of one database on another one, without using special options. Indeed, to restore a backup to a non-existent database is common method to create a copy. It is most easy done with SQL, as you can provide all necessary options, see Code snippet.

With trusted stuff, you seem not to be member of that domain, or MSSQL had problems registering itself in the Domain (e.g. because of not using an domain account for the MSSQL service). If it is no problem for you to use explicit credentials I would stick on that, needing mixed mode auth. of course.


restore database NewDB
 from disk='c:\temp\olddb.backup'
 with move 'data' to 'c:\MSSQL\Data\NewDB.mdf',
           'log'  to 'c:\MSSQL\Data\NewDB.ldf',
      replace;
 

Open in new window

0
 
audreymjhhaAuthor Commented:
Hi,
   Sorry about combining the issues. Back to the original. I am a domain administrator. I installed SQL Server Express 2008 onto my c:\ drive. I do access my PC by logging into my network. I am unable to create an odbc driver that points to the sql express instance. Nor can I upload my access database to the sql server express instance. I tried to use trusted connection and I tried to use my domain username and password but it gives my "Error 18452 - Login untrusted domain and cannot be used with windows authentication".  The only way I have been able to connect to the SQL Server instance is through "mixed mode authentication" and with the sa account. My own domain account won't connect. Do you have any suggestions? I altered both my domain as well as computer account to allow delegation but this did not help.
Thank you
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Did the restore work now?

Use a domain account for the MSSQL service, and try again.
0
 
audreymjhhaAuthor Commented:
Hi,
   I tried the code to restore the primary db to the copy db but received the error message
Msg 3234, Level 16, State 2, Line 1
Logical file 'data' is not part of database 'HomePayroll'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Creating ODBC errror:
How do I use a domain account for MSSQL service. I looked at the serveradmin in the security settings in SQL and my domain/account was listed there. Where do I need to go to be able to use a domain account for the MSSQL service?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
In Server Configuration Manager, Services, the account for Server is your domain account? If so, change it to LocalSystem, and restart the server service.

About backup, you have to look yourself how the logical names of the database are. You can do that by using
restore filelistonly from disk='c:\temp\olddb.backup'
The logical names are listed in the first column, in most cases they are called like the database, with log file appending a "_log".


0
 
audreymjhhaAuthor Commented:
I tried to change the Service to Local System but I received an error message
"WMI Provider Error - call to wmi provider returned error code 0X80074814".

I also tried to start the SQL Server agent but it timed out with "Request failed b/c service did not respond in a timely fashion. When I check the logs, it cannot connect to the instance because login is from an untrusted domain and cannot be used with windows authentication. This also would not let me change to local system. It said request not supported.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The WMI error seems to be occuring sometiimes with MSSQL 2008 Express. I've seen no workaround or solution yet.

Honestly, use mixed authentication and the sa or any other explicit created account on MSSQL, if you want to get further. Something with your domain registration is not working (as it seems), or MSSQL has no appropriate access to the domain data. Too much problems here, take the easy way!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now