Background: We have 2 servers running SQL Server 2008 R2 on Windows Server 2008 R2. I am the admin\DBA for both servers so I have the Windows "Administrator" password and the SQL "sa" password. The company has a Windows domain. However, these servers are "Stand alone" - they are not a part of the domain. A month ago we went from Win2003\ SQL2000 to this. I'm primarily a VB developer, so go easy on me with any vague security descriptions. I don't know if it matters, but when I'm on my PC in SSMS (as "sa") the "can" next to the server name only shows a white circle on it (no green arrow).
Where ever possible, I always connect with SQL Auth. "sa".
When I try to Connect to SSIS from my PC in SSMS, the only thing I am allowed to change is the server name. It forces Windows Auth. and the user name defaults to "domain\my PC user". So when IS connects (ironically I get the green arrow on the IS in SSMS) I cannot expand out the MSDB - it gets a huge error message about "failed to retreive data for this request").
First question - I'm guessing there is no fix for this because I cannot Connect to SSIS with SQL Auth. or change the Windows username to drop the domain so I can login with user name only (SQL Server is not in the domain)?
I have tables that I copy from Prod. server to backup server at night. I use to use DTS for this. They do nothing fancy - clear destination table and then copy the entire table. When I migrate some work, others do not. So I am just going to recreate them by hand. I figure the best way to do is is let the system do it so I'm using the SQL Export Wizard to build them and save them as an SSIS package (MSDB) and then schedule the Jobs to run at night.
If I run the wizard and schedule the job from my PC in SSMS, it fails at night when running - some kind of Connection errors and I think it mentions "sa". If I "Remote Desktop" into the actual server (signed on as Windows Administrator and SSMS as "sa" (the same thing I sign onto SSMS on my PC - "sa")) and setup the SSIS package from the "local" server in SSMS and schedule the job from there, the scheduled package runs fine at night.
Second Question: I really don't like having to "Remote Desktop" into the production server every time I want to run the Export wizard, create an SSIS export package and schedule it. Are there some settings or something I can do so I can do this task from my PC in SSMS?