?
Solved

Create ODBC link to SQL EXPRESS 2008

Posted on 2009-04-29
11
Medium Priority
?
2,280 Views
Last Modified: 2012-05-06
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
Comment
Question by:audreymjhha
  • 6
  • 5
11 Comments
 
LVL 71

Expert Comment

by:Qlemo
ID: 24262009
Express installs itself usually as a Named Instance, .\mssqlexpress or alike. Did you check that?
0
 

Author Comment

by:audreymjhha
ID: 24262635
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 24263320
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:audreymjhha
ID: 24272550
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 24277641
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
 

Author Comment

by:audreymjhha
ID: 24296485
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 24297643
Did the restore work now?

Use a domain account for the MSSQL service, and try again.
0
 

Author Comment

by:audreymjhha
ID: 24304522
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 24304970
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
 

Author Comment

by:audreymjhha
ID: 24358280
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
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 24455494
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Configuring network clients can be a chore, especially if there are a large number of them or a lot of itinerant users.  DHCP dynamically manages this process, much to the relief of users and administrators alike!
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question