Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rogdawg
Starting Member
23 Posts |
 Posted - 2007-11-07 : 15:56:53
|
| I am really having a difficult time understanding what is required for connections to instances of SQL Server. I know this is fairly basic stuff but, I am really struggling. I have 3 different cases where I am trying to interact with SQL at the moment.1) I am trying to use the Upsizing Wizard in MS Access to up-size data into a SQL database but, I am unable to connect.2) I am trying to connect to the same SQL Server instance from a desktop application using ADODB recordset objects.3) I am trying to copy data from a local instance of SQL server that is on my development machine (laptop) to the same server listed in the first 2 cases, which is on a LAN in our office. Here are the particulars:The server name is "Annex"The authentication mode is set to Windows Authentication. (This is what is displayed in the "Connect to Server" dialog when SQL Server Management Studio is opened, on the server).If I connect to that instance of SQL Server, and go to the Security Tab, it says that "SQL Server and Windows Authentication mode" is selected...so I am using mixed mode.If I look at the properties of the services on the server (using SQL Server Configuration Manager, for instance), I see that they are set for "Log on as: Built-in account: Network Service". This is for SQL Server (MSSQLSERVER)", SQL Server Agent, SQL Server Integration Services, SQL Server Browser, SQL Server Analysis Services (MSSQLSERVER). The SQL Server FullText Search (MSSQLSERVER) service is set for logon as "Local System" account (I read in one of the many knowledge-base articles that this is necessary).I have tried having all the services set for logon as "Local System" account also, but that doesn't work either. I have admin rights on the server. My normal windows login allows me to use SQL Server Management Studio to login to the instance of SQL that is on the server (I installed the instance, and created databases on it). So, when I use MS Access on my development machine and attempt to upsize data using Trusted Connection, I get the following error:"Cannot generate SSPI context". I have been over the knowledge-base article describing how to debug for this, and have not gotten a resolution. If I try to use a login and password from the upsizing wizard (using the exact same login and password I use to connect in Management Studio on the server), instead of Trusted Connection, I get:Login Failed for user "blah blah blah" error:18456This is really getting old. And I am thoroughly confused at this point. I have, at times been able to connect in one instance or another, only to be denied later.Can someone recommend the simplest possible set-up for connecting to SQL server on a LAN that will allow interaction from local machines and applications?I apologize for the long post but, it has been a long and confusing journey, only to get right back to where I started. |
|
|
anonymous1
Posting Yak Master
185 Posts |
Posted - 2007-11-07 : 16:13:53
|
| this may be a double hop problem. are all three machines in the same domain? are you logged onto the machine that has the access db you want to upgrade or logged onto you laptop trying to upgrade a remote access db over to a remote sql server? |
 |
|
|
rogdawg
Starting Member
23 Posts |
Posted - 2007-11-07 : 16:26:57
|
| thank you for your reply.There are only two machines involved. My local, "development", machine has the Access database on it. I have an instance of SQL Server Developer's edition installed on that machine as well. My developer machine is on the same domain as the server to which I am trying to connect.The SQL server instance on the server is also Developers Edition, and I installed if for testing purposes, so I can connect to a server that is not on my machine. Thanks again for your response |
|
|
|
anonymous1
Posting Yak Master
185 Posts |
Posted - 2007-11-07 : 16:35:10
|
| when using the wizard do you create a new database or use an existing database? are you creating an new client/server application? are both being built on your laptop? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-07 : 16:40:16
|
| What operating system are you using on the SQL Server machine(s)? If it's XP (with SP2), then you need to disable the firewall to allow remote connectivity.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
rogdawg
Starting Member
23 Posts |
Posted - 2007-11-07 : 16:58:57
|
| thanks everyone for your replies.The OS on the server is Windows Server 2003.I am trying to create a new database from the Upsizing Wizard. I am not trying to create a new application but, I don't even get to that point in the wizard. On the second screen of the wizard, it simply asks for the Server Name, the type of connection and the name that I want to give to the new database. When I hit the "Next" button after providing this information, then I get the error. I have attempted to set the SQL Instance and the Services on the server to use Windows Authentication, and then use Trusted Connection from the Wizard. And, I have tried to specify the Logon in the Services, Mixed Mode in the instance of SQL, and specify the Login ID and Password from the wizard. Neither of these approaches works. If I use Remote Desktop to connect to the server, and open SQL Server Management Studio, then connect using Windows Authentication, I am able to get into the database and do work...make edits, etc., etc.There is something basic that I am missing.Thanks again for your help. |
|
|
|
anonymous1
Posting Yak Master
185 Posts |
Posted - 2007-11-07 : 17:17:24
|
| the question about the firewall would be on your laptop. from the laptop open a cmd window and type "sqlcmd -sannex" do you get a prompt "1>" meaning you can successfully connect to that sql server from the laptop, or an error messsage? |
|
|
|
rogdawg
Starting Member
23 Posts |
Posted - 2007-11-07 : 18:00:13
|
| thanks again.I ran the sqlcmd and got >1 as a response. Also the Annex server is listed in the dropdown list of available servers in the Upsizing Wizard. So, I believe it sees the server ok.--I GOT IT TO WORK!!After I tried your suggestion, I opened SQL Server Management Studio on the server, logging on as "sa", and checked the user permissions associated with my windows logon. I granted all permissions to my windows logon, and then tried the upsizing wizard again, and it worked. I have the services set to logon as "Local System", and I am using Trusted Connection from Upsizing Wizard. So the simplest configuration seems to be working. I clearly have a lot to learn about this. Especially before I plan out a serious application architecture.Thanks, all, for your help. |
|
|
|
|
|
|
|
|
Connection issues