I have code written in X++ that calls a stored procedure and passes two parameters to it. Here's the problem:
* The calling code works with one SQL Server machine (10.1.0.11)
* The calling code does not work on another SQL Server Machine (10.2.0.31\sql2005)
Some additional information:
* The stored procedure code in 10.1.0.11 was copied and pasted into 10.2.0.31 .
* Database names the same.
* Referred to 10.2.0.31 by instance name i.e. "10.2.0.31/sql2005".
* On 10.2.0.31 compared security to 10.2.0.11 and enabled absolutely anything that could be enabled. I.e. Allow connections on server. DBO rights to databases including master. Sysadmin roles etc.
* I can create an odbc connection to the 10.2.0.31/sql2005 server via control panel --> administrative tools but just cannot connect
* I'm using windows authentication
The end result is that i still get cannot error messages about not being able to log onto that SQL Server. Any ideas from the standpoint of SQL Server security?
Ensure that under the instance properties, security, login auditing you are capturing failed logins only or both failed and successful logins. Try to rerun your code and then review the SQL Server logs. You should see a failed logging attempt with an error code. You can easily look up the code online or on BOL which will help you figure out what is wrong. If you don't see an error code, then the login attempt is not even making it to SQL, so it might be a server setting or maybe a blocked port.