| Author |
Topic |
|
GregDDDD
Posting Yak Master
120 Posts |
 Posted - 2010-02-12 : 16:35:40
|
| I do occasional reporting for a small office. They recently went from a ISAM CTree databse (Ugh!) to SQL Server 2005. They use a front-end written by the same company that set up and designed the database. I do my reporting with MS Access 2003 linking to the db via an ODBC driver.Do I need to make any setting changes on SQL Sever 05 to be able to create the ODBC connection from a computer on the network. I am able to see the SQL Server, but can't make the connection. It could just be a username/password issue, but I want to know if I should also look at the settings on the server. For instance, would I need to enable "Remote Connections" on the SQL Server 2005 Surface Area Configuration utility.Thank you,Greg |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-13 : 03:05:07
|
| "I do my reporting with MS Access 2003 linking to the db via an ODBC driver"TO the SQL box or ON the SQL box?If its TO the SQL box then the box is set up with all it needs. If it is ON the SQL box then it may not have all it needs. |
 |
|
|
GregDDDD
Posting Yak Master
120 Posts |
Posted - 2010-02-13 : 12:53:01
|
Currently - that is to say, when I was querying the CTree database - I was not on the Server when I worked in Access. I was on a separate computer on the domain.With the new set up with SQL Server 05, all of the other users use a "terminal" set up (Remote desktop?) to run the front-end software. I'm not sure at this point, how it is going to be (should be) set up for me. quote:
If it is ON the SQL box then it may not have all it needs.
Can you elaborate on this?Thank you,Greg |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-14 : 02:53:31
|
| I would have done, but sorry its not something I know about. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-02-14 : 11:03:41
|
| Yes, ensure Remote connections is set up .Also, are using sql server authentication or windows authentication? Either way , if you are testing make sure you can make a connection to the sql serverJack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
GregDDDD
Posting Yak Master
120 Posts |
Posted - 2010-02-14 : 13:24:38
|
| Thanks. I generally just use sql server authentication and then 'sa' and no password. I'm not sure how this being set up, though. I hope to get back over there next week.Greg |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-14 : 13:34:09
|
| "then 'sa' and no password"I would strongly recommend that you never do that, it leaves the security wide open.Put a seriously strong password on SA - so anyone trying to crack the passswor dfor that (well known, of course) UserID will fail.Make another user with SysAdmin permissions (and no password, if you must)Better still, create a database Role, assign a server-login to that role, on that database, and then login (from the application) using that login ID.You can then change the permissions that the Role has, assign multiple server-logins to that same Role (so you only have to create the permissions once - for all users with the same requirements) |
|
|
|
GregDDDD
Posting Yak Master
120 Posts |
Posted - 2010-02-14 : 17:05:50
|
| Thanks. I'll pass that along to the people running the show. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-14 : 17:19:48
|
| Just to clarify:Put a strong password on SA, write it down and lock it in the safe. Never use SA (unless you get locked out or somesuch).Create separate accounts for each person who needs SysAdmin access - thus you can audit which of the SysAdmin people did what, and revoke their permissions if they leave etc.Set up accounts for each Application that will connect to the database (or each user if that is more appropriate in your circumstances). Make that user a member of a database role, and set the Role to the appropriate permissions that you want to allow that user / application to have.Do not let two physical users, or two separate applications, share a login ID. That way you can remove an ID if you need to prevent it from accessing the system for any reason (left the company; fraud / abuse; changes to security arrangements; etc.) |
|
|
|
GregDDDD
Posting Yak Master
120 Posts |
Posted - 2010-02-16 : 12:52:13
|
| I still must be missing something. I'm testing this on my computer so it looks like I know what I'm doing when I go back over to the office I report for. On my computer I an running SQL Server Express 05 and 08. I have created the same log in with the same password on both instances. I have Browser services running and TCP/IP and Named Pipes are enabled. For both 05 and 08...I can make an ODBC connecting with "SQL Native Client" driver using Windows Authentication. I can not make a connection with "SQL Native Client" driver with the log in I created.I can not make a connection with the "SQL Server" driver for either Windows Authentication or the log in I created.I suppose maybe I should be happy that I can make the connection with SQL Native Client and Windows Authentication, but it would be nice to know what I'm doing wrong with the other types of connections.Greg |
|
|
|
GregDDDD
Posting Yak Master
120 Posts |
Posted - 2010-02-16 : 19:29:33
|
| I've been comparing my set up of SQL Server 05 Express on my PC to a full-blown set up of a commercial installation we have to try and figure out why I can't connect to my mine via the SQL Server driver when I can connect to the full-blown install.When I look at the properties for one of the server roles on the full-blown install there is a General and Permissions tab. I can see on the permissions tab that "Connect" is one of the permissions. On my SQL Server Express install there is never a permissions tab. Where do I set or check the permissions for the roles?Greg |
|
|
|
GregDDDD
Posting Yak Master
120 Posts |
Posted - 2010-02-16 : 21:48:34
|
I think I'm zeroing in on this. The error below shows up in the SQL Server logs. I have run every wizard and configuration utility I can find. No where do I see where I can change the SQL Server configuration so that it will accept SQL authentication AND Windows authentication . quote:
Login failed for user 'Test1'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]
Greg |
|
|
|
GregDDDD
Posting Yak Master
120 Posts |
Posted - 2010-02-16 : 22:18:51
|
| OK, so I figured out how to change the settings to allow both Windows auth and SQL Sever auth. I restarted SQL Server and SQL Server Browser and I still can not connect via SQL Server Authentication. The strange thing is, I get the same error message from the ODBC Manager when trying to connect, but it no longer writes an error to the SQL Server logs. So now I'm really stumped.SQL State 01001SQL Server Error 3ThenSQL State 08001SQL Server Error 17 |
|
|
|
GregDDDD
Posting Yak Master
120 Posts |
Posted - 2010-02-16 : 22:30:15
|
| Got it!!Under client configuration when setting up the ODBC connection I only had the name of the computer, not the name of the computer and the name of the SQL server. When setting up for the SQL Native Client it populated all of the fields correctly. For the SQL Server driver it only populated the computer name and did not add the slash SQL Server name.So here is a question: If I only know the computer name SQL server is on, but don't have the name of the SQL server instance, how could I get that? Say I'm on a client computer on the domain and I know there is a SQL server out there. Greg |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-17 : 02:53:37
|
| That may be a SQL Express thing - I think you need "SQL_Express_Name/Instance_Name"On our SQL Servers we just have one instance of SQL installed - so we can just connect to the Server_Name (and specify the PORT if it is not the default)Other than that I think you either have to have access to the server itself to see the names of the instances, or some sort of sniffer (maybe SSMS itself can detect the instance name once it can "see" the Server??) |
|
|
|
GregDDDD
Posting Yak Master
120 Posts |
Posted - 2010-02-17 : 11:17:49
|
| Kristen,It could be because I'm running both SQL Server 05 & 08 at the same time. I'll know next week when I go back to the office I do reporting for. This whole process, while frustrating, has been very helpful. There is another office that I want to do work for and I was never able to connect to their server, but really didn't put much effort in to it at the time. I think I can get in now.Just in case anyone else reads this, in order to allow both Windows Authentication and SQL Server Authentication in 05 or 08 you go to SQL Server Management Studio, right click on the server name (top node in the left pane) and chose properties. Then click on the 'Security' tab and you have a choice of server authentication methods. When reading about this on-line, though, I found articles that said I could set this from the 05 Surface Area Configuration Tool. I couldn't find the option in that tool.Greg |
|
|
|
|
ODBC Connection